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",
COUNT(inquiries.id) AS "Inquiries"
WHERE YEAR(inquiries.contact_time) = "2014"
) AS "2014",
(SELECT COUNT(inquiries.id) AS "Inquiries"
WHERE YEAR(inquiries.contact_time) = "2015"
) AS "2015"
GROUP BY MONTH(inquiries.contact_time)
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.