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”)
GROUP BY “column_name1”;
Let’s explain by using the following table:
Table Name: StoreInformation
We wish to find total sales for every store. To perform so, we are going to give query:
SELECT Store_Name, SUM(Sales)
GROUP BY Store_Name;
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”)
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)
GROUP BY Txn_Date;
For these given exercises, assume we a table with name RegionSales having following data:
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?
2. SELECT Region, SUM(Total_Sales) FROM Region_Sales GROUP BY Region;
3. SELECT AVG(Total_Sales) FROM Region_Sales WHERE Region IN (‘East’,’West’);