acatx acatx - 4 months ago 10
MySQL Question

Multi-Series (Column) MySQL Query Won't Summarize Properly

I have several years worth of data in a table (inquiries). Every entry has a contact_time field that is the timestamp of their email contact. I'm trying to build monthly or weekly summary data for plotting on a multi-series graph. To that end, I need to see the month or week number in the first column with the respective data from 2014 in the second column, and from 2015 in the third column, etc.

SELECT MONTH(inquiries.contact_time) AS "Date",
(SELECT
COUNT(inquiries.id) AS "Inquiries"
FROM inquiries
WHERE YEAR(inquiries.contact_time) = "2014"
) AS "2014",
(SELECT COUNT(inquiries.id) AS "Inquiries"
FROM inquiries
WHERE YEAR(inquiries.contact_time) = "2015"
) AS "2015"
FROM inquiries
GROUP BY MONTH(inquiries.contact_time)


All I'm seeing is the total count for each year in all of the rows. Any help is appreciated.

Answer

You're seeing the total count for the year because your subqueries are not related to the month for the outer query's grouping.

I would write the query this way:

SELECT MONTH(contact_time) AS `Date`,
  SUM(YEAR(contact_time)=2014) AS `2014`,
  SUM(YEAR(contact_time)=2015) AS `2015`
FROM inquiries
GROUP BY MONTH(contact_time)

Explanation: the COUNT() of a specific set of rows is the same as the SUM() of 1's for those rows. And MySQL boolean expressions return the integer 1 for true.