SQL GROUP BY

The GROUP BY clause is used to tell SQL what level of granularity the aggregate function should be calculated in. The level of granularity is represented by the columns in the SELECT statement that are not aggregate functions.

Syntax

The syntax for GROUP BY is,

SELECT "column_name1", "function name" ("column_name2")
FROM "table_name"
GROUP BY "column_name1";

More than one column can be specified in the GROUP BY clause, and more than one function can be included.

GROUP BY is a command that can trip up many beginners, as it is often possible to have a SQL statement with the correct GROUP BY syntax, yet get the wrong results. A good rule of thumb when using GROUP BY is to include all the non-aggregate function columns in the SELECT statement in the GROUP BY clause.

Examples

We use the following table for our examples.

Table Store_Information

Example 1: GROUP BY a single column

We want to find total sales for each store. To do so, we would key in,

SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name;

Result:

Example 2: GROUP BY multiple columns

In Example 1, there is only one column associated with GROUP BY. It is possible to have two or more columns associated with GROUP BY.

We want to find total sales for each product at each store. To do so, we would key in,

SELECT Store_Name, Product_ID, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name, Product_ID;

Result:

Example 3: GROUP BY multiple columns and multiple functions

We want to find total sales and the average sales for each product at each store. To do so, we would key in,

SELECT Store_Name, Product_ID, SUM(Sales), AVG(Sales)
FROM Store_Information
GROUP BY Store_Name, Product_ID;

Result:

Example 4: Group by month / date / week

A common use of the GROUP BY function is on a time period, which can be month, week, day, or even hour. This type of query is often combined with the ORDER BY keyword to provide a query result that shows a time series.

For example, to find total daily sales from Store_Information, we use the following SQL:

SELECT Txn_Date, SUM(Sales)
FROM Store_Information
GROUP BY Txn_Date
ORDER BY Txn_Date;

Result:

Exercises

For these exercises, assume we have a table called Region_Sales with the following data:

Table Region_Sales

1. Which of the following order is correct for a SQL statement?
a) SELECT...FROM...WHERE...GROUP BY...ORDER BY
b) SELECT...FROM...ORDER BY...WHERE...GROUP BY
c) SELECT...FROM...WHERE...ORDER BY...GROUP BY
d) SELECT...WHERE...FROM...GROUP BY...ORDER BY

2. Write a SQL statement that calculates the total dollar sales amount for each region. What is the result?

3. Write a SQL statement that calculates the average annual dollar sales amount for just the East region and the West region. What is the result?