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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
HainKurtConnect With a Mentor Sr. 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
 
PortletPaulCommented:
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
All Courses

From novice to tech pro — start learning today.