Get maximum value of a GROUP BY query without subquery in mySQL

Question

hi,

i have some queries which group datasets and count them, e.g.

  SELECT COUNT(*)
    FROM `table`
GROUP BY `column`

now i have the number of rows for which column is the same, so far so good.

problem is: how do i get the aggregate (min/max/avg/sum) values for those “grouped” counts. using a subquery sure is the easiest, but i was wondering if this is possible within this single query

Answer 1

For min and max you can ORDER BY and fetch the first row. For sum/avg/other aggregates you would need a subquery.

Answer 2

In MySQL you should be able to do this all at once. My tests seem to indicate that this works.

| date       | hits |
|-------------------|
| 2009-10-10 |    3 |
| 2009-10-10 |    6 |
| 2009-10-10 |    1 |
| 2009-10-10 |    3 |
| 2009-10-11 |   12 |
| 2009-10-11 |    4 |
| 2009-10-11 |    8 |
 -------------------

SELECT COUNT(*), MAX(hits), SUM(hits) FROM table GROUP BY date

| COUNT(*)  | MAX(hits) |
|-----------|-----------|
|        4  |         6 |
|        3  |        12 |
 -----------------------

SUM, MIN and AVG also work. Is this what you are looking for?