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 type" ("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 the 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_NameProduct_IDSalesTxn_Date
Los Angeles11500Jan-05-1999
Los Angeles2500Jan-05-1999
San Diego1250Jan-07-1999
Los Angeles1300Jan-08-1999
Boston1700Jan-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_NameSUM(Sales)
Los Angeles2300
San Diego250
Boston700

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_NameProduct_IDSUM(Sales)
Los Angeles11800
Los Angeles2500
San Diego1250
Boston1700

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_NameProduct_IDSUM(Sales)AVG(Sales)
Los Angeles11800900
Los Angeles2500500
San Diego1250250
Boston1700700

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_DateSUM(Sales)
Jan-05-19992000
Jan-07-1999250
Jan-08-19991000

Exercises

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

Table Region_Sales

RegionYearOrdersTotal_Sales
West20131560325000
West20141820380000
North2013790148000
North2014995185000
East20131760375000
East20142220450000
South20131790388000
South20141695360000

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




Copyright © 2017   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact