Solved

have one statement on one line

Posted on 2014-12-23
13
169 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 53

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 53

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 53

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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