Tutorial on SQL Group By Command

 
This is an aggregate function. This SQL statement is used of a case when we wish to calculate the total sales for each store. We need to perform two things: First, we need to ensure we select the store name as well as total sales. And second, we need to ensure that all the sales figures are grouped by stores.
 
The SQL syntax for the same is given below :
 
SELECT “column_name1”, SUM(“column_name2”)
FROM “table_name”
GROUP BY “column_name1”;
 
Let’s explain by using the following table:

Table Name: StoreInformation

StoreName Sales TxnDate
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999

 
We wish to find total sales for every store. To perform so, we are going to give query:
 
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name;

Result:

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

GROUP BY Multiple Columns

In this mentioned example, there is only one column related with GROUP BY. It might be possible that you have two columns or more associated with GROUP BY.
 
The GROUP BY keyword is usually used when we are choosing multiple columns from a table (or tables) and minimum one arithmetic operator appears in the SELECT statement.
 
Such operators consist of COUNT, SUM, MAX, MIN, and AVG.
 
When that occurs, we require to GROUP BY all the other chosen columns, i.e., all columns except the one(s) operated on by the arithmetic operator. As such, it is imperative to note that we might have two columns or more associated with GROUP BY.
 
And the general syntax is shown below:
 
SELECT “column_name1”, “column_name2”, … “column_nameN”, Function(“column_nameN+1”)
FROM “table_name”
GROUP BY “column_name1”, “column_name2”, … “column_nameN”;

GROUP BY Month / Date / Week

A popular use of the GROUP BY function is on a time period that can be month, week, day, or even hour. Such type of query is frequently combined with the ORDER BY keyword to give a query result which shows a time series.
 
For instance, to calculate the total daily sales from StoreInformation, we can shoot the following SQL query:
 
SELECT Txn_Date, SUM(Sales)
FROM Store_Information
GROUP BY Txn_Date;

Outcome:

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

Practice Exercises

For these given exercises, assume we a table with name RegionSales having following data:

Table RegionSales:

Region Year Orders TotalSales
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. Find which of the following order is accurate 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. Script a SQL statement which calculates the total dollar sales amount for every region. What is the outcome?
 
3. Script a SQL statement which calculates the average annual dollar sales amount only for the East region and the West region. What is an outcome?

Answers

1. a)
 
2. SELECT Region, SUM(Total_Sales) FROM Region_Sales GROUP BY Region;

Region SUM(Total_Sales)
West 705000
North 333000
East 825000
South 748000

 
3. SELECT AVG(Total_Sales) FROM Region_Sales WHERE Region IN (‘East’,’West’);
 
AVG(Total_Sales)
382500

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.