SQL Server : conditonal aggregation different to mysql?

Question

I posted a quested yesterday which was answered very well by Gordon Linoff.

Basically I was using MySQL and needed to remove a where statement because it was affecting all columns when that's not what was needed, conditional aggregation was my answer which worked with MySQL.

The query was as follows:

select 
    sum(amount) as `total amount for all items sold`,
    count(amount) as `Total items sold`,
    sum(sale_made between '2018-07-02' and '2018-07-05') as `half total days`
from
    sales;

Again, this worked perfectly in MySQL but with SQL Server 2017, I get an error here '2018-07-05')

Refer to this: https://stackoverflow.com/a/5125106/3454439. Note the date uses '/'. — Just a HK developer, Jul 10 '18 at 08:39
I dont work with SQL Server much but i am guessing you may have to write your date differently :/ i am not too sure — connorg98, Jul 10 '18 at 08:39
thanks for your response guys, I am actually declaring variables eg - declare startDate datetime declare endDate datetime set endDate = getdate() set startDate = dateadd(day,-11,endDate) --get previous month from current date . I have been messing around with sql server before and never had an issue writing dates like that before — Charlie Ansell, Jul 10 '18 at 08:54
sum(case when sale_made between '2018-07-02' and '2018-07-05' then 1 else 0 end) — jarlh, Jul 10 '18 at 08:54
that seemed to have worked - thank you. I have never seen this before, would this be used for multiple conditions? ie. sum(amount case when sale_made between '2018-07-02' and '2018-07-05' then 1 else 0 end) — Charlie Ansell, Jul 10 '18 at 09:06

score 0 · Accepted Answer · answered Jul 10 '18 at 11:01

In MySQL expressions, that can be true or false, evaluate to 1 or 0. So sale_made between '2018-07-02' and '2018-07-05' in MySQL can be either 1 or 0 depending on whether sale_made is in the respective range or not. And that numerics, 0 or 1, can be summed.

SQL Server doesn't have such a thing to cast a logical expression to a number. Such expressions are simply illegal in any context not expecting a logical expression. So you have to do the "translation" yourself with a CASE.

CASE
  WHEN sale_made BETWEEN '2018-07-02'
                         AND '2018-07-05' THEN
    1
  ELSE
    0
END

score 0 · Answer 2 · answered Jul 10 '18 at 11:19

You just need a case expression:

select sum(amount) as [total amount for all items sold],
       count(amount) as [Total items sold],
       sum(case when sale_made between '2018-07-02' and '2018-07-05' then 1 else 0 end) as [half total days]
from sales;

Note that this also changes the escape characters for the column aliases.

SQL Server : conditonal aggregation different to mysql?

2 Answers2