have one statement on one line

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
lulu50Asked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
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
 
fabriciofonsecaCommented:
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
 
fabriciofonsecaCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
lulu50Author Commented:
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
 
lulu50Author Commented:
it doesn't like the comma "Network Management]),"

and it doesn't like the AS " AS [Product Management]) "
0
 
lulu50Author Commented:
I am using SQL 2012
0
 
fabriciofonsecaCommented:
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
 
lulu50Author Commented:
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
 
lulu50Author Commented:
Great!!!
0
 
HainKurtSr. System AnalystCommented:
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
 
lulu50Author Commented:
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
 
HainKurtSr. System AnalystCommented:
this is closed, check your other question... I posted there...

but

cast(round(100.00 * 2 / 3, 0) as int) >>> 67
0
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.