When I run my SELECT statement it runs the IFNULL part thrice and the SUM CASE part twice. I know it has something to do with too many LEFT JOIN in the statement. But unable to find a solution to the issue.
It is hard to explain the problem so I have included sqlfiddle link.
SELECT
IFNULL(SUM(d.quantity),0) as dispatch,
SUM(CASE WHEN t.valuefor='alpha' THEN t.quantity ELSE 0 END) as alpha,
SUM(CASE WHEN t.valuefor='beta' THEN t.quantity ELSE 0 END) as beta
FROM conversion as c
LEFT JOIN sale as s ON s.conversionid = c.id
LEFT JOIN dispatch as d ON d.saleid = s.id
LEFT JOIN test as t ON t.conversionid = c.id
Current Output
dispatch alpha beta
1800 1400 80
Desired Output
dispatch alpha beta
600 700 40
Aucun commentaire:
Enregistrer un commentaire