We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Combining SQL Queries

Medium Priority
285 Views
Last Modified: 2012-05-11
I have 5 queries that I need to run for each of 4 Customer ID's.  Each query returns a single record (a total).  I need to return the results of all 5 queries for a given Customer ID on a single row in a record set.  For example:
CustID = 1
Query1 returns 100
Query2 returns 200
Query3 returns 300
Query4 returns 400
Query 5 returns 500

My end result should be:

CustomerID, Category10, Category11, Category12, Category13, Category14
1, 100, 200, 300,400, 500

Once I get this solved, I'll need to repeat these 5 queries for another 3 Customer ID's, so ultimately, I'll need to get a final result grid with 4 rows (one per customer ID).  For this example, I'm only focusing on the first row of data.  However, if you could elaborate on how I might expand this to include other customer IDs (e.g. 2, 3, 4, 5), that would be great!  Thanks!

So, the larger solution might look like this example SQL result grid below:

CustomerID, Category10, Category11, Category12, Category13, Category14
1, 100, 200, 300,400, 500
2, 105, 218, 301,422, 503
3, 185, 166, 330,411, 502
4, 115, 545, 340,405, 503
5, 85, 335, 320,405, 40

The 5 queries that would be run for each Customer ID are attached.  Thanks!
SELECT	SUM(D.BalanceToShip) AS 'Category10'
FROM 
	(
	SELECT	(o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
	FROM	Orders o
	WHERE	O.ShipDate1 <= GETDATE()+7
			AND O.CategoryID = 10
			AND O.POTypeID IN (2,7)
			AND O.CustID = 1
			AND ORDERCLOSED=0
	) D


SELECT	SUM(D.BalanceToShip) AS 'Category11'
FROM 
	(
	SELECT	(o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
	FROM	Orders o
	WHERE	O.ShipDate1 <= GETDATE()+7
			AND O.CategoryID = 11
			AND O.POTypeID IN (2,7)
			AND O.CustID = 1
			AND ORDERCLOSED=0
	) D


SELECT	SUM(D.BalanceToShip) AS 'Category12'
FROM 
	(
	SELECT	(o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
	FROM	Orders o
	WHERE	O.ShipDate1 <= GETDATE()+7
			AND O.CategoryID = 12
			AND O.POTypeID IN (2,7)
			AND O.CustID = 1
			AND ORDERCLOSED=0
	) D


SELECT	SUM(D.BalanceToShip) AS 'Category13'
FROM 
	(
	SELECT	(o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
	FROM	Orders o
	WHERE	O.ShipDate1 <= GETDATE()+7
			AND O.CategoryID = 13
			AND O.POTypeID IN (2,7)
			AND O.CustID = 1
			AND ORDERCLOSED=0
	) D 


SELECT	SUM(D.BalanceToShip) AS 'Category14'
FROM 
	(
	SELECT	(o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
	FROM	Orders o
	WHERE	O.ShipDate1 <= GETDATE()+7
			AND O.CategoryID = 14
			AND O.POTypeID IN (2,7)
			AND O.CustID = 1
			AND ORDERCLOSED=0
	) D

Open in new window

Comment
Watch Question

use UNION

Author

Commented:
UNION won't work as it will combine the numbers down like this:

Category
100
200
300
400
500

I need to assemble my 5 queries to become a single "row" in a result grid.  That will represent the 5 query results for a given customer id (e.g. 1).  I then need to run those 5 queries 3 more times (for each subsequent customer ID).  In the end, I need SQL to give me one record set with 6 columns (the customer id and 5 categories) and 4 rows as shown in example.  
So you need to join these five tables. Dont you have KEYS between these tables?

Author

Commented:
There are not 5 tables here.  All data is coming from 1 table called Orders.  Please pay careful attention to the details of the problem and the queries.  This is tricker than it might appear at first glance.
SELECT      custid, Category10, Category11, Category12, Category13, Category14
FROM
      (
      SELECT      custid, CategoryID, (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
      FROM      Orders o
      WHERE      O.ShipDate1 <= GETDATE()+7
                  AND O.POTypeID IN (2,7)
                  AND ORDERCLOSED=0
      group by custid, CategoryID
      ) D
      pivot (sum(balancetoship) for CategoryID in (Category10, Category11, Category12, Category13, Category14)) p
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
first add id to your queries

SELECT      SUM(D.BalanceToShip) AS 'Category10'
FROM
      (
      SELECT      (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
      FROM      Orders o
      WHERE      O.ShipDate1 <= GETDATE()+7
                  AND O.CategoryID = 10
                  AND O.POTypeID IN (2,7)
                  AND O.CustID = 1
                  AND ORDERCLOSED=0
      ) D

-->

SELECT      O.CustID, SUM(D.BalanceToShip) AS 'Category10'
FROM
      (
      SELECT      O.CustID, (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]
      FROM      Orders o
      WHERE      O.ShipDate1 <= GETDATE()+7
                  AND O.CategoryID = 10
                  AND O.POTypeID IN (2,7)
                  AND O.CustID = 1
                  AND ORDERCLOSED=0
      ) D

then use joins

select q1.CustID, Category10, Category11, Category12, Category13, Category14
from query1 q1
left join query2 q2 on q1.CustID=q2.CustID
left join query3 q2 on q1.CustID=q3.CustID
left join query4 q2 on q1.CustID=q4.CustID

Author

Commented:
hyphenpipe - this is interesting.  I'll give it a try and report back.  The only thing I don't see is where I might add the Customer ID values.  I only care about 4 of them.  Perhaps adding a line in the where clause like:

AND CustID in (1, 2, 3, 4)

???
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I suggest create a query which gives

Query1:
CustID Category10
1          100
2          105
3          185
4          115
5          85

you can use "where CustID in (1,2,3,4)" if you wish

Query2:
CustID Category11
1          200
2          218
3          166
4          545
5          335

and so on... then use:

select q1.CustID, Category10, Category11, Category12, Category13, Category14
from query1 q1
left join query2 q2 on q1.CustID=q2.CustID
left join query3 q3 on q1.CustID=q3.CustID
left join query4 q4 on q1.CustID=q4.CustID
left join query5 q5 on q1.CustID=q5.CustID



Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
Hi
You can use it as following
Thanks
Select 1 as CustID, 

(SELECT  SUM(D.BalanceToShip)   
FROM   
        (  
        SELECT  (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]  
        FROM    Orders o  
        WHERE   O.ShipDate1 <= GETDATE()+7  
                        AND O.CategoryID = 10  
                        AND O.POTypeID IN (2,7)  
                        AND O.CustID = 1  
                        AND ORDERCLOSED=0  
        ) D  
) AS 'Category10', 
  
(SELECT  SUM(D.BalanceToShip)  
FROM   
        (  
        SELECT  (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]  
        FROM    Orders o  
        WHERE   O.ShipDate1 <= GETDATE()+7  
                        AND O.CategoryID = 11  
                        AND O.POTypeID IN (2,7)  
                        AND O.CustID = 1  
                        AND ORDERCLOSED=0  
        ) D  
)  AS 'Category11',
  
(SELECT  SUM(D.BalanceToShip)  
FROM   
        (  
        SELECT  (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]  
        FROM    Orders o  
        WHERE   O.ShipDate1 <= GETDATE()+7  
                        AND O.CategoryID = 12  
                        AND O.POTypeID IN (2,7)  
                        AND O.CustID = 1  
                        AND ORDERCLOSED=0  
        ) D  
  
)  AS 'Category12',  
(SELECT  SUM(D.BalanceToShip) 
FROM   
        (  
        SELECT  (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]  
        FROM    Orders o  
        WHERE   O.ShipDate1 <= GETDATE()+7  
                        AND O.CategoryID = 13  
                        AND O.POTypeID IN (2,7)  
                        AND O.CustID = 1  
                        AND ORDERCLOSED=0  
        ) D   
  
  ) AS 'Category13',  

(SELECT  SUM(D.BalanceToShip) 
FROM   
        (  
        SELECT  (o.poqty1*o.poqty1mult)-ISNULL((SELECT SUM(s.amount) FROM CustShipments s WHERE s.indexid = o.indexid),0) AS [balancetoship]  
        FROM    Orders o  
        WHERE   O.ShipDate1 <= GETDATE()+7  
                        AND O.CategoryID = 14  
                        AND O.POTypeID IN (2,7)  
                        AND O.CustID = 1  
                        AND ORDERCLOSED=0  
        ) D
) AS 'Category14'

Open in new window

Author

Commented:
wdosanjos,

This is very elegant solution.  You did a gret job carefully reading my question and the requirements.  The query works perfectly and was exactly what I needed.  

Thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.