September 13, 2011 at 11:24 am
Just wondering,
I have this query:
select
ProductID, SUM(OrderQty) nOrderQty, SUM(ScrappedQty) nScrappedQty
from
Production.WorkOrder
group by ProductID, OrderQty, ScrappedQty
But then I'd have this result (snippet):
ProductIDnOrderQtynScrappedQty
3 1410 0
3 2400 0
3 3500 0
3 600 12
3 480 8
-- a lot of more rows here
I don't like that. I need the complete aggregates. So I build another select statement and group this resultset:
select
ProductID, SUM(nOrderQty) nOrderQty, SUM(nScrappedQty) nScrappedQty
from
(
select
ProductID, SUM(OrderQty) nOrderQty, SUM(ScrappedQty) nScrappedQty
from
Production.WorkOrder
group by ProductID, OrderQty, ScrappedQty
) a
group by ProductID
order by ProductID asc
Then I would have the desired output.
ProductIDnOrderQtynScrappedQty
3 911890 1031
316 236002 736
324 234734 585
327 117367 571
328 62302 143
Question: this grouping of groups method I use, is there a better/standard/wiser way to achieve the final result?
September 13, 2011 at 11:32 am
scrap those from the group by and use only 1 query
, OrderQty, ScrappedQty
September 13, 2011 at 11:35 am
In other words...
select ProductID, SUM(OrderQty) AS SumOrderQty, SUM(ScrappedQty) AS SumScrappedQty
from Production.WorkOrder
group by ProductID
It's very unusual for a column to both be an aggregate and in the group by statement. Usually that's a mistake.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 11:42 am
What the heck? Did I had a brainfart or something?
Lol, thanks guys anyway.
September 13, 2011 at 11:45 am
Rokh (9/13/2011)
What the heck? Did I had a brainfart or something?Lol, thanks guys anyway.
We'll let you take the call on this one. 😀
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply