SQL GROUP BY Clause | Aggregate Data in SQL Queries

SQL > SQL Commands > 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

Store_Name	Product_ID	Sales	Txn_Date
Los Angeles	1	1500	Jan-05-1999
Los Angeles	2	500	Jan-05-1999
San Diego	1	250	Jan-07-1999
Los Angeles	1	300	Jan-08-1999
Boston	1	700	Jan-08-1999

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:

Store_Name	SUM(Sales)
Los Angeles	2300
San Diego	250
Boston	700

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:

Store_Name	Product_ID	SUM(Sales)
Los Angeles	1	1800
Los Angeles	2	500
San Diego	1	250
Boston	1	700

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:

Store_Name	Product_ID	SUM(Sales)	AVG(Sales)
Los Angeles	1	1800	900
Los Angeles	2	500	500
San Diego	1	250	250
Boston	1	700	700

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:

Txn_Date	SUM(Sales)
Jan-05-1999	2000
Jan-07-1999	250
Jan-08-1999	1000

Exercises

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

Table Region_Sales

Region	Year	Orders	Total_Sales
West	2013	1560	325000
West	2014	1820	380000
North	2013	790	148000
North	2014	995	185000
East	2013	1760	375000
East	2014	2220	450000
South	2013	1790	388000
South	2014	1695	360000

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?

Next: SQL HAVING

This page was last updated on October 11, 2024.

SQL GROUP BY

Syntax

Examples

Example 1: GROUP BY a single column

Example 2: GROUP BY multiple columns

Example 3: GROUP BY multiple columns and multiple functions

Example 4: Group by month / date / week

Exercises

AdBlock Detected!