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.
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.
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
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
posted by recursion at 1:53 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:
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
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
Thanks for all the tips, I'm working on the query right now. Currently it's
and it... isn't quite working. I'll keep trying.
posted by tmcw at 2:19 PM on November 15, 2007
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.
posted by tmcw at 2:21 PM on November 15, 2007
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
This thread is closed to new comments.
posted by recursion at 1:45 PM on November 15, 2007