Combine 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Combine 2 queries

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-06 : 13:42:25

Is it possible to combine these 2 queries?

SELECT A.slspsn_no, IsNull(B.SalesAmount,0) As SalesAmount
FROM ARSLMFIL_SQL A
LEFT OUTER JOIN
(
SELECT SUM(Sls_Amt) AS SalesAmount, slspsn_no
FROM dash_product_sales
WHERE MONTH(Inv_Dt) = MONTH('12/01/2004')
AND YEAR(Inv_Dt) = YEAR('12/31/2004')
AND prod_cat IN ('o1')
GROUP BY slspsn_no
) AS B
ON A.slspsn_no = B.slspsn_no

SELECT SUM(salesgoal) AS TotalSalesGoal
FROM dash_sales_goal
WHERE salesmonth = '12/01/2004'
AND Slspsn_No IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')

The results would look like
slspsn_no SalesAmount TotalSalesGoal
1 50 500
2 25 500
3 100 500
and so on...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-06 : 13:52:08

you could use a join... for better results post DDL, DML and expected results based on sample data


SELECT A.slspsn_no, IsNull(B.SalesAmount,0) As SalesAmount, C.TotalSalesGoal
FROM ARSLMFIL_SQL A
LEFT OUTER JOIN
( 
SELECT SUM(Sls_Amt) AS SalesAmount, slspsn_no
FROM dash_product_sales
WHERE MONTH(Inv_Dt) = MONTH('12/01/2004')
AND YEAR(Inv_Dt) = YEAR('12/31/2004')
AND prod_cat IN ('o1') 
GROUP BY slspsn_no
) AS B
ON A.slspsn_no = B.slspsn_no
inner join 
(
SELECT SUM(salesgoal) AS TotalSalesGoal
FROM dash_sales_goal
WHERE salesmonth = '12/01/2004'
AND Slspsn_No IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')
) C on A.slspsn_no = C.slspsn_no