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 |
+--------+-------------+
