July 20, 2011 at 1:28 pm
I have what is probably a simple select statement for anyone who has experience writing sql. Given the following fictious table
Customer
Status
Qty
The Status field has two values: Sold and Available.
I need to write a Select statement that will return Customer, QtySold, QtyAvailble for a given customer.
My first thought was to loop through all the records for one customer and do something like the following psuedo code.
If Status = "Sold"
QtySold = QtySold + Qty
Else
QtyAvailable = QtyAvailable + Qty
I know that there must be a better way to do this. Can anyone offer me any direction?
Thanks
July 20, 2011 at 3:00 pm
This will get you closer. It may not be the exact format you are looking for, but give it a try for starters.
SELECT
Customer,
Status,
SUM(Qty) AS Quantity
FROM FictitousTable
GROUP BY Customer, Status
Good luck,
Steve
July 20, 2011 at 3:01 pm
This sounds like a fairly easy thing to do using a case operator. If you want some tested and working code you need to post some ddl, sample data and desired output based on the sample data. Please read the article in my signature for best practices on posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2011 at 3:03 pm
I'll take a guess..
SELECT SUM(CASE WHEN Status = 'Sold' THEN QtySold + Qty ELSE 0 END) As Sold,
SUM(CASE WHEN Status = 'Available' THEN QtyAvailable + Qty ELSE 0 END) As Available
, rest of columns
FROM dbo.Table
GROUP BY rest of columns
July 20, 2011 at 3:44 pm
Thank you for the suggestions. I got called out of the office for the next day or so. I will try the suggestions when I get back to the office. I really appreciate the help.
July 21, 2011 at 2:57 am
Ninja's solution will work , better to use case rather then looping through it. 😎
July 22, 2011 at 7:35 am
I am still not in the office so I can't try out Ninja's solution. I do however have one question. I don't have a great deal of experience when it comes to sql. I have a question about the use of the case statement. Why does the case statement need the "Qty ELSE 0" when computing QtySold and QtyAvailable?
Thanks
July 22, 2011 at 7:42 am
It doesn't, but I use it for clarity.
The default value is NULL when the else is not used.
It would yield the same results either way.
Now here's what the code does :
if check for something = true then use this value else 0 END
This code runs for all the rows that satisfy the joins and where clause.
now the difference here is that I wrap the "if" with SUM(if...).
That's basically the old way to pivot rows into columns. Now there are new operators for that but I still use the same old way.
July 22, 2011 at 8:38 am
Thanks for the help. I can't wait to try it when I get to the office.
July 25, 2011 at 11:29 am
Ninja, thanks for the help. Worked like a charm.
July 25, 2011 at 11:42 am
Glad it worked.
have a great day.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply