question

zyk avatar image
zyk asked

sum with multiple case statement

i'm trying to get the same result set from my original query to a case statement. Original qry: the result return $80,160 select sum(Total Cost) from table1 where date >= '7/21/2016' and customer = 'new customer' and sales like 's%' modified qry: the result return 1918 only select sum(case when TotalCost >0 then 1 when customer = 'new customer' then 1 when sales like 's%' then 1 else 0 end) from table1 where date >= '7/21/2016' Thanks for the help
sql-server-2008sumcase-statement
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

With no details how do you expect anybody to be able to help? Here is a good place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
It looks like the case statement is used to show all customers instead of just new customers and sales like 's%' while calculating the sum of total cost for only those which satisfy the criteria, returning zero for other rows. If this is what you want then take a look at what you are summing up, it happens to be number 1 instead of TotalCost. Please restate your case statement like this to see the results you probably expect: select sum( case when Customer = 'new customer' and sales like 's%' then TotalCost else 0 end ) from table1 where date >= '7/21/2016' Please note the TotalCost, not the literal 1 is being summed up. Oleg
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you so much for the help .. that works.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.