Summing and cases and aggregates, oh my.

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 2005 Forums

Transact-SQL (2005)

Summing and cases and aggregates, oh my.

Author

Topic

bleisenschen
Starting Member

2 Posts

Posted - 2009-02-11 : 14:07:56

Here is my sql:
SELECT o.id, 'Total' =
case
when (o.Order_Date >= b.SaleStartDate and o.Order_Date <= b.SaleEndDate) then sum(ol.quantity * b.saleprice)
else sum(ol.quantity * b.price)
end
FROM Orders o
INNER JOIN Order_Lines ol ON o.ID = ol.Orders_ID
join books b on ol.Books_ISBN = b.ISBN
WHERE (o.Promotions_ID = '42')
group by o.id, o.Order_Date, b.SaleStartDate, b.SaleEndDate
ORDER BY o.ID DESC

This returns data, but with multiple IDs (for example):
ID Total
36133 $50.85
36133 $6.93
36133 $4.44
36133 $.50
36133 $2.01

Here is why they're separate totals based upon the group by...(same info as above, but with the SaleStartDate and SaleEndDate fields added in.)
ID SaleStartDate SaleEndDate Total
36133 NULL NULL $50.85
36133 2005-04-11 2009-06-30 $6.93
36133 2006-04-01 2009-06-30 $4.44
36133 2008-03-24 2008-06-30 $.50
36133 2009-02-02 2009-02-10 $2.01

If I try to add a sum() to the total column, I get:
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SELECT o.id, sum(
case
when (o.Order_Date >= b.SaleStartDate and o.Order_Date <= b.SaleEndDate) then sum(ol.quantity * b.saleprice)
else sum(ol.quantity * b.price)
end) 'Total'
FROM lpwebuser.Orders o
INNER JOIN lpwebuser.Order_Lines ol ON o.ID = ol.Orders_ID
join lpwebuser.books b on ol.Books_ISBN = b.ISBN
WHERE (o.Promotions_ID = '42')
group by o.id, o.Order_Date, b.SaleStartDate, b.SaleEndDate
ORDER BY o.ID DESC

What I would like though, is all of the Totals for each ID combined into one row. How would I rewrite this so I don't get multiple IDs (rows with the same id)?

Thank you!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-11 : 14:24:00

You are grouping by:
group by o.id, o.Order_Date, b.SaleStartDate, b.SaleEndDate

so if the o.Order_Date, b.SaleStartDate, b.SaleEndDate differ within each o.id, you'll get a record for each group. So just eliminate them from your group by

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 14:24:53

[code]SELECT O.ID,
SUM(CASE
WHEN (O.ORDER_DATE >= B.SALESTARTDATE
AND O.ORDER_DATE <= B.SALEENDDATE) THEN (OL.QUANTITY * B.SALEPRICE)
ELSE (OL.QUANTITY * B.PRICE)
END) 'Total'
FROM LPWEBUSER.ORDERS O
INNER JOIN LPWEBUSER.ORDER_LINES OL
ON O.ID = OL.ORDERS_ID
JOIN LPWEBUSER.BOOKS B
ON OL.BOOKS_ISBN = B.ISBN
WHERE (O.PROMOTIONS_ID = '42')
GROUP BY O.ID
ORDER BY O.ID DESC[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-02-11 : 14:27:54

[code]select id, sum(total)
from (
SELECT o.id, 'Total' =
case
when (o.Order_Date >= b.SaleStartDate and o.Order_Date <= b.SaleEndDate) then sum(ol.quantity * b.saleprice)
else sum(ol.quantity * b.price)
end
FROM Orders o
INNER JOIN Order_Lines ol ON o.ID = ol.Orders_ID
join books b on ol.Books_ISBN = b.ISBN
WHERE (o.Promotions_ID = '42')
group by o.id, o.Order_Date, b.SaleStartDate, b.SaleEndDate
)t
group by id order by id desc[/code]

bleisenschen
Starting Member

2 Posts

Posted - 2009-02-11 : 14:33:33

Thank you, I knew it was something simple I was missing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources