Translate to MySQL ?
I tried this translation of your pseudocode but it returns NULL when there is actually data. Anything wrong with my syntax or logic?
SELECT LastTeachingOrg, YEAR(Date),
SUM(CASE WHEN MONTH(Date) = 01 THEN Income ELSE 0 END) AS Jan, SUM(CASE WHEN MONTH(Date) = 02 THEN Income ELSE 0 END) AS Feb, SUM(CASE WHEN MONTH(Date) = 03 THEN Income ELSE 0 END) AS Mar, SUM(CASE WHEN MONTH(Date) = 04 THEN Income ELSE 0 END) AS Apr, SUM(CASE WHEN MONTH(Date) = 05 THEN Income ELSE 0 END) AS May, SUM(CASE WHEN MONTH(Date) = 06 THEN Income ELSE 0 END) AS Jun, SUM(CASE WHEN MONTH(Date) = 07 THEN Income ELSE 0 END) AS Jul, SUM(CASE WHEN MONTH(Date) = 08 THEN Income ELSE 0 END) AS Aug, SUM(CASE WHEN MONTH(Date) = 09 THEN Income ELSE 0 END) AS Sep, SUM(CASE WHEN MONTH(Date) = 10 THEN Income ELSE 0 END) AS Oct, SUM(CASE WHEN MONTH(Date) = 11 THEN Income ELSE 0 END) AS Nov, SUM(CASE WHEN MONTH(Date) = 12 THEN Income ELSE 0 END) AS Dec,
COUNT(*) AS Total
FROM
SELECT LastTeachingOrg, YEAR(Date), MONTH(Date), Income FROM salarydb
WHERE (`salarydb`.`Username` = `kp`)
GROUP BY LastTeachingOrg, YEAR(Date), MONTH(Date) ) salarydb
WHERE (`salarydb`.`Username` = `kp`)
GROUP BY salarydb.LastTeachingOrg, YEAR(Date)
WITH ROLLUP
ORDER BY salarydb.LastTeachingOrg, YEAR(Date);
|