Solved

have one statement on one line

Posted on 2014-12-23
13
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN 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:Huseyin KAHRAMAN
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:Huseyin KAHRAMAN
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 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