Calculate Sum Of Hour Values Per Day, Month, Year

Yii 1.1 General Discussion
1

I have a table which contain hour values per day. For each hour I have a column going from hour 1 to 24.

The table looks simplified like this:

I want to calculate the sum of hour0, hour1, hour2 etc. per day, per month and per year. Right now to get the sum per hour I use this SQL query:

How can I calculate the sum of hour0, hour1, hour2 etc. per day, per month and per year?

2

I guess you could add month and year fields and change your GROUP BY clause.




SELECT YEAR(date) AS year, MONTH(date) AS month, ...

...

GROUP BY year, month

or for grouping by year:




SELECT YEAR(date) AS year, ...

...

GROUP BY year

I can’t remember whether the SELECT aliases will be available in the GROUP BY clause, so you might have to GROUP BY YEAR(date), MONTH(date) if not.

If performance is likely to be a problem, you might want to consider adding month and year fields to the table and populating them once from the date.

3

That worked perfect. Many thanks :)