mysql query error: SQL sum with condition

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)

Answer

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 ;