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.
| Author |
Topic |
|
bh0526
Yak Posting Veteran
71 Posts |
 Posted - 2011-08-11 : 15:06:53
|
| I have the following SQL statement:SELECT ITEMID ,SUM(Qty) as JanSalesFrees FROM InvoiceDetail WHERE InvoiceDate >= '01-01-2011' AND InvoiceDate <= '01-31-2011' AND SALESPRICE = 0 GROUP BY ITEMID ORDER BY ItemIDThis works fine. I get my total of qty where the price was 0. But now I want a column next to JanSalesFrees that contains the total qty for January regardless of what the sales price was. I cannot seem to get this to work. Can anyone help?Bob |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-11 : 15:12:24
|
Put the condition in the select part like this:SELECT ITEMID, SUM(CASE WHEN SALESPRICE = 0 THEN Qty ELSE 0 END ) AS JanSalesFrees, SUM(Qty) AS JanSalesFROM InvoiceDetailWHERE InvoiceDate >= '01-01-2011' AND InvoiceDate <= '01-31-2011' --AND SALESPRICE = 0GROUP BY ITEMIDORDER BY ItemID |
 |
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-08-11 : 15:23:00
|
| Sorry, I missed something in my original post. I only want to select items that have at least one item sold in January that had a salesprice of 0. So I cannot comment out the "SalesPrice = 0" So if I have 10 items sold in January but only 4 had at least 1 sale with 0 sales price, I want to display 4 rows. I then want the sum of qty for items with $0 sales price and another sum of total qty regardless of sales price.Bob |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-11 : 15:50:50
|
Add a HAVING clause just above the ORDER BY clause as:HAVING SUM(CASE WHEN SALESPRICE = 0 THEN 1 ELSE 0 END) > 0 |
|
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-08-11 : 16:13:24
|
| Ah, that did it! Thanks!I had tried the HAVING before but without the CASE. That did the trick. |
|
|
|
|
|
|
SUM problem