SQL GROUP BY Statement

Aggregate functions (such as SUM) often require the addition of a GROUP BY statement.

GROUP BY Statement

The GROUP BY statement is used to combine aggregate functions, grouping the result set based on one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SQL GROUP BY Example

We have the following "Orders" table:

O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter

Now, we want to find the total amount for each customer (total orders).

We want to use the GROUP BY statement to group by customers.

We use the following SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders
GROUP BY Customer

The result set is similar to this:

Customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000

Pretty cool, isn't it?

Let's see what happens if we omit the GROUP BY:

SELECT Customer, SUM(OrderPrice) FROM Orders

The result set is similar to this:

Customer SUM(OrderPrice)
Bush 5700
Carter 5700
Bush 5700
Bush 5700
Adams 5700
Carter 5700

The above result set is not what we need.

Why can't we use the above SELECT statement? Explanation as follows: The above SELECT statement specifies two columns (Customer and SUM(OrderPrice)). "SUM(OrderPrice)" returns a single value (the total of the "OrderPrice" column), while "Customer" returns 6 values (each corresponding to each row in the "Orders" table). Therefore, we do not get the correct result. However, as you have seen, the GROUP BY statement solves this problem.

GROUP BY More Than One Column

We can also apply the GROUP BY statement to more than one column, like this:

SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders
GROUP BY Customer, OrderDate