All months, now.

November 15, 2007 1:28 PM

Sum of data for all months, including months with no rows, in MySQL?

Hi, I've come across this problem a few times, and here's the thing:

I'll do a query, for instance, SUM(distance) for each month, group by month. This'll give me results with the sum of distance for each month with rows. But I'd like to present the sums of all months, including non-active months. So far my only option seems to be to create an array of months in PHP, iterate through that, and, for each month, search for a result in the MySQL query result. This works, but is there a cleaner design pattern, or a MySQL trick that might come in handy?
Thanks for any help.

posted by tmcw to computers & internet (7 comments total)

You can create a table with all values you want (all the months, in this case) and do an outer join to it. Then if there is at least one row for each month in the result set.
posted by recursion at 1:45 PM on November 15, 2007

What does your table look like? Do you have, e.g., the columns DISTANCE (int) and RACE_DATE (date) or some such? Or are you storing things more strangely. The obvious hackish to do it is to create a table called something like MONTHS with twelve rows in it, then use MySQL's built in MONTH(date) function to do a left join. I'm sure there's a more elegant way that doesn't introduce an unnecessary join, as well.
posted by snarkout at 1:47 PM on November 15, 2007

let me add one more trick I learned recently. SUM(distance) is going to be NULL for the months where you have no data, so to pretty up the results you can instead use COALESCE(SUM(distance), 0). Then you get 0 for the months that you have no data for.
posted by recursion at 1:53 PM on November 15, 2007

I asked a very similar question some time ago.
posted by boo_radley at 1:54 PM on November 15, 2007

What everyone else said. If your table was called 'tbl' with columns 'distance' int and 'submitted' date, you could create another table months with 'id' int, insert ids 1-12 and:

CREATE VIEW tbl_by_month AS
SELECT MONTHNAME(CONCAT('2000/', months.id, '/1')) AS month,
COALESCE(SUM(distance), 0) AS distance
FROM tbl RIGHT JOIN months ON MONTH(tbl.submitted) = months.id
GROUP BY months.id
ORDER BY months.id;

The MONTHNAME thing is kinda strange but it's locale aware. You could store the month name too if it turns out to be expensive to build that string, cast it to a date, and get the month name back from it.
posted by moift at 2:13 PM on November 15, 2007 [1 favorite has favorites]

Thanks for all the tips, I'm working on the query right now. Currently it's

SELECT COALESCE(SUM(distance),0), COALESCE(MONTH(`runs`.date),0), `range`.id FROM `range` left join `runs` on MONTH(`runs`.date) = `range`.id GROUP BY MONTH(`runs`.date)

and it... isn't quite working. I'll keep trying.
posted by tmcw at 2:19 PM on November 15, 2007

Ah, I think I got it.

SELECT COALESCE(SUM(distance),0), COALESCE(MONTH(`runs`.date),0), `range`.id FROM `range` left join `runs` on MONTH(`runs`.date) = `range`.id WHERE `range`.id <>
posted by tmcw at 2:21 PM on November 15, 2007