Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Combining SQL Queries

Posted on 2011-04-18
11
Medium Priority
?
268 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

0
Comment
Question by:dpmoney
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35418999
use UNION
0
 

Author Comment

by:dpmoney
ID: 35419060
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.  
0
 
LVL 9

Expert Comment

by:radcaesar
ID: 35419088
So you need to join these five tables. Dont you have KEYS between these tables?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dpmoney
ID: 35419105
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.
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35419124
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35419142
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
0
 

Author Comment

by:dpmoney
ID: 35419170
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)

???
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35419251
Please try the following. It's similar to @hyphenpipe.

SELECT CUSTID, [10] AS CATEGORY10, [11] AS CATEGORY11, [12] AS CATEGORY12, [13] AS CATEGORY13, [14] AS CATEGORY14
  FROM 
	(
	SELECT O.CUSTID,
	       O.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.CATEGORYID BETWEEN 10 AND 14
	   AND O.POTYPEID IN (2,7)
	   AND O.CUSTID IN (1, 2, 3, 4)
	   AND ORDERCLOSED=0
	) AS SRC
	PIVOT
	(
		SUM([BALANCETOSHIP])
		FOR CATEGORYID IN ([10], [11], [12], [13], [14])
	) AS PVT

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35419377
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



0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35419418
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

0
 

Author Closing Comment

by:dpmoney
ID: 35419552
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!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

569 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