jeudi 10 novembre 2016

Sum the value in if/else mysql select statement

Not sure how to MAX the date inside if/else statement of MySQL query.

Here my select statement:

SELECT month(Max(a.planned_date)) as month, a.planned_date as date, year(Max(a.planned_date)) as year,  Sum(if(month(a.planned_date) = 1, b.value,0)) AS Jan, Sum(if(month(a.planned_date) = 2, b.value,0)) AS Feb, Sum(b.value) as total FROM b INNER JOIN a ON b.id = a.id INNER JOIN c ON b.reg_id = c.reg_id WHERE year(a.planned_date) < date(NOW()) GROUP BY c.reg_id

All I am trying to do is to have a cross table of Jan to Dec, but return a MAX date to bring the value. However, the way I have done it the value is doubling up the value.

Please help your advise is appreciated.

Thank you

Aucun commentaire:

Enregistrer un commentaire