mysql query error: SQL sum with condition

Question

This is my code:

select assignedstfid, staffname, staffsymbol, SUM(case where bookingstarttime > '2021-03-31' then TIMESTAMPDIFF (minute, bookingstarttime, bookingendtime) else 0 end) as lengthofworking from crm.bookingtable, crm.staffinfo where staffinfo.staffid = bookingtable.assignedstfid GROUP BY assignedstfid ;

and there is a syntax error about 'select' saying that 'select is not valid in this position for this server version, expecting " ( " with' ...

How could I fix this?

the query is based on this question(SQL sum with condition)

score 2 · Accepted Answer · answered Apr 03 '21 at 23:03

One obvious error in your query is case where instead of case when.

In addition:

The GROUP BY columns should be consistent with the unaggregated SELECT expressions.
Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.
Use table aliases and qualify column references.

So, this might work:

select bt.assignedstfid, s.staffname, s.staffsymbol, 
       sum(case when bookingstarttime > '2021-03-31'
                then timestampdiff(minute, bt.bookingstarttime, bt.bookingendtime) else 0
           end) as lengthofworking
from crm.bookingtable bt join
     crm.staffinfo s 
     on s.staffid = bt.assignedstfid 
group by bt.assignedstfid, s.staffname, s.staffsymbol ;

mysql query error: SQL sum with condition

1 Answers1