Grouping rows together with GROUP BY

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!

 

Next chapter: MySQL functions >>

Previous chapter: Multiple WHERE conditions

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar