This is the simplest grouping query pattern. For column foo, display the first (smallest), last (largest) or average value of column bar.
SELECT foo, MIN(bar) AS bar FROM tbl GROUP BY foo
Return the highest bar value for each foo, ordering top to bottom by that value:
SELECT foo, MAX(bar) AS Count FROM tbl GROUP BY foo ORDER BY Count DESC;
Ditto for AVG(), COUNT() etc. The pattern is easily extended for multiple grouping column expressions.
MySQL introduced the SQL extension GROUP_CONCAT(), which makes short work of listing items in groups. For example, given a table of suppliers and the parts they make...
CREATE TABLE supparts(supID char(2),partID char(2));
INSERT INTO supparts VALUES
('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s1','p5'),('s1','p6'),
('s2','p1'),('s2','p2'),('s3','p2'),('s4','p2'),('s4','p4'),('s4','p5');
list suppliers for each part:
SELECT partID, GROUP_CONCAT(supID ORDER BY supID) AS Suppliers FROM supparts GROUP BY partID;
+--------+-------------+
| partID | Suppliers |
+--------+-------------+
| p1 | s1,s2 |
| p2 | s1,s2,s3,s4 |
| p3 | s1 |
| p4 | s1,s4 |
| p5 | s1,s4 |
| p6 | s1 |
+--------+-------------+
|
