Also in aboutSQL:
This week, we continue our warp-speed introduction to SQL by looking at the
GROUP BY clause. In the previous article we talked about the
ORDER BY clause for sorting query results and covered aggregate functions. This week, we begin to put those two ideas together.
GROUP BY clause is typically used to combine database records with identical values in a specified field into a single record, usually for the purposes of calculating some sort of aggregate function. The syntax is remarkably similar to the
ORDER BY clause.
SELECT ... GROUP BY column_name
While the syntax is simple, the reality is more complex. Each of the database fields involved in the
SELECT statement must either be operated on by an aggregate function or otherwise reduced to a single value for all members of the records creating a group.
Counting all of the employees of a large corporation using
GROUP BY on the
office_location field would work and so would simple returning the values of
office_location after grouping on that field.
Selecting the names of those individual employees using
GROUP BY would not work because the names for everyone in a single
office_location group do not reduce to a single value. There are ways to combine queries to create that sort of a result, but we aren't quite there yet. To add another layer of complexity, you can group on more than one column, just like with the
ORDER BY clause.
Another problem with
GROUP BY is the behavior of null values. When a column of data is grouped, null values count just like any other value -- and thus all of the "NULLs" are put in one group for the purposes of aggregate functions. This is actually a remarkably sensible way for the underlying SQL algorithms to work and should seem intuitive, but it is important to remember that you'll get one more aggregate value than you expect if the database contains null values unless you somehow filter out those null values (which we'll talk about next week).
Let's revisit a query I talked about a couple weeks ago -- generating a sales report broken down by a particular employee:
SELECT SUM(SalesAmount) WHERE EmployeeID=3
which, given the following table of data
would return a value of $3,055. This sort of query could be useful in a drill-down or detail report, but it is much more likely that a sales manager would ask for the total sales for all of their employees. We could write a separate SQL statement for each, but it should now be obvious to you that the
GROUP BY clause would be much more useful for this scenario. Given the same data set, we can use this SQL statement:
SELECT EmployeeID, SUM(SalesAmount) GROUP BY EmployeeID
which would return the result set
Note that I selected both the aggregated sum of the
SalesAmount field for each employee, but also the
EmployeeID field. This is allowed since the
EmployeeID is identical for each set of records in a group. We couldn't
SalesDate field since the records in each group consist of different values for the
SalesDate field. Of course if we grouped the records by
SalesDate to create a daily report.
SELECT SalesDate, SUM(SalesAmount) GROUP BY SalesDate
SalesDate field which is identical for each of the records in a group, but now we cannot
EmployeeID since each day's sales will typically be made up of more than one employee's sales.
Next we, we'll combine aggregate functions and the
GROUP BY clause along with searching using the
HAVING clause to complete our whirlwind introduction to data-processing with SQL. After we reach that point, we'll switch gears completely back to database fundamentals and start talking about relationships, keys, and joining tables together. Until then, feel free to contact me with comments and questions.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Copyright © 2009 O'Reilly Media, Inc.