Using subqueries to select 2 different sums

[sgk17]

I'm trying to create a query to find out the quantity sold of each of our products. The query takes two date ranges and should return the quantity sold totals for each of the date ranges. I'm trying to use a subquery but the way I'm writing it gives me errors. I understand why I get the errors, but I can't think of a way to do it. Here's a simplified version of what I'm trying to do.

Select S.Stock_ID, Sum(OD.QTY) AS QTY,
(Select Sum(OD.QTY) AS QTY,
From Stock S, Order_Detail OD, Orders O
Where S.Stock_ID = OD.Stock_ID and O.Order_ID=OD.Order_ID
Group By S.Stock_ID) AS QTY2
From Stock S, Order_Detail OD, Orders O
Where S.Stock_ID = OD.Stock_ID and O.Order_ID = OD.Order_ID
Group By S.Stock_ID

Any help would be greatly appreciated. Thanks

 

[nigelrivett]

Which data ranges?

the group by in the subquery doesn't do anything as it has a single Stock_ID


Select S.Stock_ID, Sum(OD.QTY) AS QTY,
QTY2 = sum(case when o.dte between @d1 and @d2 then OD.QTY else 0 end)
From Stock S, Order_Detail OD, Orders O
Where S.Stock_ID = OD.Stock_ID and O.Order_ID = OD.Order_ID
Group By S.Stock_ID

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sgk17]

Sorry, both the where clauses should have a date range in them that's different. And the group_by in the subquery doesn't do anything except throw an error. But what I want is the sum of the quantity listed twice: each quantity is added up and filtered on a different date range for each stock item. The query I posted was a kind of best guess attempt at what it might look like.

 

[nigelrivett]

The query I gave you will give the total qty for the stockIDs and the sum for a date range
If you have a differet date range for each stock item then put them into a table and join on it in the query.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[maswien]

Select S.Stock_ID, Sum(OD1.QTY) AS QTY1, Sum(OD2.QTY) AS QTY2
From Stock S, Order_Detail OD1, Order_Detail OD2, Orders O
Where S.Stock_ID = OD1.Stock_ID and O.Order_ID = OD1.Order_ID and OD1.order_date between range1
and S.Stock_ID = OD2.Stock_ID and O.Order_ID = OD2.Order_ID and OD2.order_date between range2
Group By S.Stock_ID

 

[maswien]

OR:

Select S.Stock_ID,
Sum(case when OD.order_date between range1 then OD.QTY else 0 end) AS QTY1,
Sum(case when OD.order_date between range2 then OD.QTY else 0 end) AS QTY2
From Stock S, Order_Detail OD, Orders O
Where S.Stock_ID = OD.Stock_ID and O.Order_ID = OD.Order_ID
Group By S.Stock_ID

 

[ESquared]

That last one looks nice mjia.