Solved

have one statement on one line

Posted on 2014-12-23
13
164 Views
Last Modified: 2014-12-23
Hi,

      
I have two queries that I need combine together and add the total

this is the first query

	SELECT COUNT(AR.ApplicationID) AS [Provider and Network Management]
								FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID 
									   INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
									   BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
								WHERE  AR.Retired = 0  And AR.BusinessProcessId = 20 
					

Open in new window



The output for the first query is this:

Provider and Network Management
28

The second Query is this

	SELECT COUNT(AR.ApplicationID) AS [Product Management]
								FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID 
									   INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
									   BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
								WHERE  AR.Retired = 0  And AR.BusinessProcessId = 40 

Open in new window



The output for that query is this
Product Management
2


How can I combine the two queries to get this output and the total

this is the output that I want

Provider and Network Management                 Product Management          Total
28                                                                             2                                                 30

Thank you
0
Comment
Question by:lulu50
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40515587
Create a new table called "Table1" and add just 1 record.

The run the following SQL:

SELECT (SELECT COUNT(AR.ApplicationID)
                                                FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                                                         INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
                                                         BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
                                                WHERE  AR.Retired = 0  And AR.BusinessProcessId = 20) AS [Provider and Network Management]),
            (SELECT COUNT(AR.ApplicationID)
                                                FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                                                         INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
                                                         BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
                                                WHERE  AR.Retired = 0  And AR.BusinessProcessId = 40) AS [Product Management])
FROM Table1
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40515599
ops... forgot to add the Total. With the total now:


SELECT (SELECT COUNT(AR.ApplicationID)
                                                FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                                                         INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
                                                         BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
                                                WHERE  AR.Retired = 0  And AR.BusinessProcessId = 20) AS [Provider and Network Management]),
            (SELECT COUNT(AR.ApplicationID)
                                                FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                                                         INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
                                                         BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
                                                WHERE  AR.Retired = 0  And AR.BusinessProcessId = 40) AS [Product Management]),
 [Provider and Network Management] +  [Product Management] AS [Total]
FROM Table1
0
 

Author Comment

by:lulu50
ID: 40515600
I have two errors

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.
0
 

Author Comment

by:lulu50
ID: 40515606
it doesn't like the comma "Network Management]),"

and it doesn't like the AS " AS [Product Management]) "
0
 

Author Comment

by:lulu50
ID: 40515607
I am using SQL 2012
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40515609
the problem was the "("... I miseed one :(

Try this:

SELECT ((SELECT COUNT(AR.ApplicationID)
                                                FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                                                         INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
                                                         BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
                                                WHERE  AR.Retired = 0  And AR.BusinessProcessId = 20) AS [Provider and Network Management]),
            ((SELECT COUNT(AR.ApplicationID)
                                                FROM   AppRepository AR INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                                                         INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId INNER JOIN
                                                         BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
                                                WHERE  AR.Retired = 0  And AR.BusinessProcessId = 40) AS [Product Management]),
 [Provider and Network Management] +  [Product Management] AS [Total]  
FROM Table1
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40515761
here:

with 
t1 as (your 1st query here),
t2 as (your 2nd query here)
select [Provider and Network Management], [Product Management], [Provider and Network Management] + [Product Management] as Total
from t1, t2

Open in new window


test

with 
t1 as (select 28 [Provider and Network Management]),
t2 as (select 2 [Product Management])
select [Provider and Network Management], [Product Management], [Provider and Network Management] + [Product Management] as Total
from t1, t2

Provider and Network Management	Product Management	Total
28	2	30

Open in new window

0
 

Author Comment

by:lulu50
ID: 40515866
fabriciofonseca  -  Sorry I tried it again but I still getting an error


HainKurt - I tried your way it works great!!!

Hainkurt I want to do the next step is the percentage.  

for  28 I need to display another colum with percentage

28 * 100/30 to get the percentage of "Provider and Network Management"

2 * 100/30 to get the percentage of "Product Management"

I want to open a new question for it.  

Please help me
0
 

Author Closing Comment

by:lulu50
ID: 40515867
Great!!!
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40515928
here it is

with 
t1 as (select 28 [Provider and Network Management]),
t2 as (select 2 [Product Management])
select [Provider and Network Management], [Product Management], 
       [Provider and Network Management]+[Product Management] as Total,
	   100 * [Provider and Network Management] / ([Provider and Network Management]+[Product Management]) as [Percentage of Provider and Network Management],
	   100 * [Product Management] / ([Provider and Network Management]+[Product Management]) as [Percentage of Product Management]
from t1, t2

Provider and Network Management	Product Management	Total	Percentage of Provider and Network Management	Percentage of Product Management
28	2	30	93	6

Open in new window

0
 

Author Comment

by:lulu50
ID: 40515957
HainKurt,

Great beautiful!!! but just one little thing the 6% need to be rounded to 7%
so 93% and 7% will equal 100%

(2*100)/30 = 6.6  I need this to be rounded off to be 7% instead of 6%
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40515990
this is closed, check your other question... I posted there...

but

cast(round(100.00 * 2 / 3, 0) as int) >>> 67
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40515995
Sorry for the lateness of this, but it's possible to avoid 1 pass through the source tables.

If you use a case expression inside the COUNT() function you can get conditional counts, e.g.

                  COUNT(CASE
                        WHEN AR.BusinessProcessId = 20 THEN AR.ApplicationID
                  END) AS BUSINESSPROCESSID20

                  which will only add 1 to the count if AR.BusinessProcessId = 20

Apply this to both conditional counts you can arrive at the count for AR.BusinessProcessId = 20 or AR.BusinessProcessId = 40 or AR.BusinessProcessId IN (20,40) as a single query. Then with that you "push it down one layer" so that you can calculate the percentages.
SELECT
      BusinessProcessId20 AS [PROVIDER AND NETWORK MANAGEMENT]
    , (BusinessProcessId20 * 100) / total
    , BusinessProcessId40 AS [PRODUCT MANAGEMENT]
    , (BusinessProcessId40 * 100) / total
    , total
FROM (
            SELECT
                  COUNT(CASE
                        WHEN AR.BusinessProcessId = 20 THEN AR.ApplicationID
                  END) AS BUSINESSPROCESSID20
                , COUNT(CASE
                        WHEN AR.BusinessProcessId = 40 THEN AR.ApplicationID
                  END) AS BUSINESSPROCESSID40
                , COUNT(AR.ApplicationID) AS TOTAL
            FROM AppRepository AR
                  INNER JOIN ApplicationRepository_BusinessSubProcess ARBSP ON AR.ApplicationID = ARBSP.ApplicationID
                  INNER JOIN BusinessSubProcess BSP ON ARBSP.SubProcessID = BSP.BusinessSubProcessId
                  INNER JOIN BusinessProcess BP ON BSP.BusinessProcessId = BP.BusinessProcessId
            WHERE AR.Retired = 0
            AND AR.BusinessProcessId IN (20, 40)
      ) AS DERIVED
;

Open in new window


Not seeking a change to selected answer, just thought an alternative was worth mentioning.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now