Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

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 |
share|improve this answer
    
Thanks. But I suspect this could be somehow slow, because GROUP BY wouldn't use index for WEEK or MONTH fucntions. Are there a faster code? –  porton Dec 7 '12 at 14:39
    
@porton if you want this data by month and week, there is no other way to get this. –  bluefeet Dec 7 '12 at 14:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.