The use of GROUP BY in MySQL
- by Gustav Bertram
I'm fishing for a comprehensive and canonical answer for the typical "mysql group by?" question.
Here is some sample data:
TABLE A
+------+------+----------+-----+
| id | foo | bar | baz |
+------+------+----------+-----+
| 1 | 1 | hello | 42 |
| 2 | 0 | apple | 96 |
| 3 | 20 | boot | 11 |
| 4 | 31 | unicorn | 99 |
| 5 | 19 | pumpkin | 11 |
| 6 | 88 | orange | 13 |
+------+------+----------+-----+
TABLE B
+------+------+
| id | moo |
+------+------+
| 1 | 1 |
| 2 | 99 |
| 3 | 11 |
+------+------+
Demonstrate and explain the correct use of the GROUP BY clause in MySQL. Touch upon the following points:
The use of MIN, MAX, SUM, AVG
The use of HAVING
Grouping by date, and ranges of dates
Grouping with an ORDER BY
Grouping with a JOIN
Grouping on multiple columns
Bonus points for references to other great answers, the MySQL online manual, and online tutorials on GROUP BY.
