ΔλЛ ΔλЛ - 2 months ago 9
MySQL Question

Multiple aggregations in a single SQL statement

I have an SQL table with roughly the following structure:

id | datetime | value
---------------------


What I would like is get a results with the following structure:

id | value_avg_overall | value_avg_last_month | value_avg_last_week
-------------------------------------------------------------------


Currently I am using
JOIN
, that is constructing a query similar to that one:

SELECT *
FROM (
SELECT_OVERALL INNER JOIN SELECT_LAST_MONTH ON SELECT_OVERALL.id = SELECT_LAST_MONTH.id
)
INNER JOIN SELECT_LAST_WEEK ON SELECT_LAST_WEEK.id = SELECT_LAST_MONTH.id;


I have simplified the query to make it more readable, that
SELECT_*
statements are simple
SELECT
statements using
GROUP BY
together with
AVG()
for respective time ranges.

Is this the most effective way to perform multiple aggregations and return all of them in a single result set in MySQL?

Answer

This is called conditional aggregation:

select 
  id, 
  avg(value) as value_avg_overall,
  avg(case when datetime > date_sub(curdate(), interval 1 month) then value end)
    as value_avg_last_month,
  avg(case when datetime > date_sub(curdate(), interval 1 week) then value end)
    as value_avg_last_week
from mytable
group by id;
Comments