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 Angeles11500  Jan-05-1999
  Los Angeles2500  Jan-05-1999
  San Diego1250  Jan-07-1999
  Los Angeles1300  Jan-08-1999
  Boston1700  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 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_Name     Product_ID     SUM(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_Name   Product_ID   SUM(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_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 June 19, 2023.




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