Search code examples
mysqlsqlanalytics

MySQL return a table of Group By Functions?

My goal is to see how the average changes as chunks of data are chopped off. I have a basic query that returns a set of averages:

SELECT AVG(col1), AVG(col2), AVG(col3), COUNT(*)
FROM table
WHERE col1 > 100;

In concept, I want to vary the WHERE clause and thus get a table of results:

SELECT AVG(col1), AVG(col2), AVG(col3), COUNT(*)
FROM table
WHERE col1 > 100;

SELECT AVG(col1), AVG(col2), AVG(col3), COUNT(*)
FROM table
WHERE col1 > 200;

except for many conditions, and this only returns the last row. How can I iterate to obtain many sets of averages?

Solution

  • You would do this by creating a table of the cut-off values. Something like:

    select c.cutoff, avg(t.col1), avg(t.col2), avg(t.col3), count(*)
from (select 100 as cutoff union all
      select 200
     ) c left join
     table t
     on t.col1 > c.cutoff
group by c.cutoff;