Group By

The group by clause is used to combine the information from your database together. What it does is to take all of the rows in the resultset that have the same value in all of the fields specified in the group by clause and return a single row for that group.

Just what information gets returned isn't fully determined by the group by clause. All that clause determines is which rows will get grouped together. What actually gets returned for each group is determined by the use of aggregate functions. The following aggregate functions are available in mySQL.

When you use a group by clause in a database call then every field you are returning from the call is either in the group by clause (in which case it has a single value to return for each row) or it is a field not in the group by and needs to have one of the agregate functions applied to it in order to tell the query how all of the values returned for each group are to be combined together in order to give a single value to be returned for that row of the resultset.

In the following example field1 can be returned as normal as it is in the group by clause and therefore there will only be one value for this field for each row to be returned. The other fields are not referenced in the group by and so can have multiple values for each group. In the example we return the maximum value for field2 and disregard all the other values for that field in the group and we concatenate all of the values for field3 together to return all the values as a single value.

SELECT field1, MAX(field2), GROUP_CONCAT(field3) GROUP BY field1


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow