9.3.14 Grouping rows together with GROUP BYThis is NOT the latest copy of this book; click here for the latest version.
Most types of data have repeating data that defines some element of it. For example, consider a table that stores the round score for members in a golf club - each time they complete a round (play all 18 holes) their score gets entered into the table, along with their member ID, date of play, etc. The scores are of course individual to themselves, but the member IDs will recur.
Now you can, as you know, limit output of those results by member by using the WHERE clause, but what if you want all the scores printed out, but with the member ID bringing them together?
Here's our table created in MySQL, outputted using "SELECT * FROM golfscores;":
+----+----------+-------+-------------+
| ID | MemberID | Score | DateEntered |
+----+----------+-------+-------------+
| 1 | 1 | 70 | 1089448558 |
| 2 | 1 | 74 | 1089448562 |
| 3 | 43 | 69 | 1089448569 |
| 4 | 13 | 77 | 1089448576 |
| 5 | 1 | 72 | 1089448584 |
| 6 | 13 | 80 | 1089448590 |
| 7 | 1 | 69 | 1089448599 |
+----+----------+-------+-------------+
Now, here's the same table again, this time extracted using the GROUP BY clause with "SELECT * FROM golfscores GROUP BY MemberID;":
+----+----------+-------+-------------+
| ID | MemberID | Score | DateEntered |
+----+----------+-------+-------------+
| 1 | 1 | 70 | 1089448558 |
| 4 | 13 | 77 | 1089448576 |
| 3 | 43 | 69 | 1089448569 |
+----+----------+-------+-------------+
As you can see, each MemberID gets printed out just once, and the Score and DateEntered fields that are printed are the values from the first row with each MemberID in. So, although MemberID 1 has scores of 74, 72, and 69, it's their first score of 70 that's printed - not very helpful!
However, in the creation of that query, MySQL does indeed read all the rows for each MemberID and could do something else with these values. By default, though, it just prints the first value - to do anything more we need to get into the realm of MySQL functions!
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
