-1

I have a query that captures the sum with multiple condition (see below), however, i would like to do the same thing but within a case statement if possible.

Original Query:

select
sum(Total Cost)
from Table 
where date between '7/20/2016' and '7/21/2016'
and customer = "New Customer"
and sales like 's%'

I'm trying to put that in one case statement.

select
sum(case when total cost is not null then 1 else
    case when customer = 'New Customer' then 1 else
    case when sales like 's%' then 1 else
    end end end
from table
where date between '7/20/2016' and '7/21/2016'

Thanks for the help

ZYK
  • 9
  • 1
  • 1
  • 2
  • 1
    Edit your question and provide sample data and desired results. Your logic doesn't really make sense . . . you seem to want a `case` expression that always returns 1. – Gordon Linoff Jul 23 '16 at 17:27

1 Answers1

0

I think you are looking for something like this:

SELECT SUM(CASE WHEN customer = 'New Customer' AND sales LIKE 's%' THEN [Total Cost] END)
FROM Table
WHERE [date] BETWEEN '2016-07-20' AND '2016-07-21'

Please note - the second query in your question is equivalent to count, not to sum. Also, When using strings to represent dates, always use yyyy-mm-dd format, to avoid ambiguity - In sql server - 1/4/2016 might be April 1st but might also be January 4th. However, 2016-04-01 can only be April 1st.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • thank you for the response ... base on the query you provided, i'm not getting the desired result... it's returning null value when i run the first query(original), the total cost sums up to $80,463 – ZYK Jul 24 '16 at 05:09
  • Then please edit your question to include sample data as DDL + DML and desired results. – Zohar Peled Jul 24 '16 at 05:16