Mysql – Get week/month data

MySQL

There is a table (MySQL).

It's simplified structure:

day DATE
val INT UNSIGNED

I need the average val for:

  • every week containing a date from the table;
  • every month containing a date from the table.

What is the simplest way to do this?

Best Answer

If you are looking to return the average val for each week and every month that has a date in the table, then you can implement the following.

Sample data:

CREATE TABLE yourtable (`day` datetime, `val` int);

INSERT INTO yourtable (`day`, `val`)
VALUES
    ('2012-01-01 00:00:00', 465),
    ('2012-01-02 00:00:00', 896),
    ('2012-08-15 00:00:00', 678),
    ('2012-09-01 00:00:00', 324),
    ('2012-12-02 00:00:00', 74),
    ('2012-12-03 00:00:00', 65),
    ('2012-12-04 00:00:00', 35);

Weekly Average:

To get the average by week, I would GROUP BY both the week number and the year, using the MySQL functions week() and year(). Then if you have data from multiple years the average will be shown by year as well (otherwise your values could be skewed). Your query would be similar to this:

select week(day) Week,
  year(day) Year,
  avg(val) WeekAvg
from yourtable
group by week(day), year(day)

See SQL Fiddle with Demo. The result is:

| WEEK | YEAR | WEEKAVG |
-------------------------
|    1 | 2012 |   680.5 |
|   33 | 2012 |     678 |
|   35 | 2012 |     324 |
|   49 | 2012 |      58 |

Monthly Average:

Now to get the average val by month, you can use the MySQL functions monthname() and year().

select monthname(day) Month, 
  year(day) Year, 
  avg(val) AvgValue
from yourtable
group by monthname(day), year(day)

See SQL Fiddle with Demo. The result is:

|     MONTH | YEAR | AVGVALUE |
-------------------------------
|    August | 2012 |      678 |
|  December | 2012 |       58 |
|   January | 2012 |    680.5 |
| September | 2012 |      324 |

Combined Result:

You did not specify if you want this data in the same query. But if you do, then you can easily use a UNION ALL to consolidate the data into one result set. In the UNION ALL query, I added a column to identify the data as either month or week (but you can remove that if needed):

select monthname(day) Month, 
  year(day) Year, 
  avg(val) AvgValue,
  'MonthAvg' Source
from yourtable
group by monthname(day), year(day)
union all
select week(day) Week,
  year(day) Year,
  avg(val) WeekAvg,
  'WeekAvg' Source
from yourtable
group by week(day), year(day)

See SQL Fiddle with Demo The UNION ALL will produce a combined result set similar to this:

|     MONTH | YEAR | AVGVALUE |   SOURCE |
------------------------------------------
|    August | 2012 |      678 | MonthAvg |
|  December | 2012 |       58 | MonthAvg |
|   January | 2012 |    680.5 | MonthAvg |
| September | 2012 |      324 | MonthAvg |
|         1 | 2012 |    680.5 |  WeekAvg |
|        33 | 2012 |      678 |  WeekAvg |
|        35 | 2012 |      324 |  WeekAvg |
|        49 | 2012 |       58 |  WeekAvg |
Related Question