Group by clause | Group by clause command in sql - sql - sql tutorial - learn sql
- The usage of SQL GROUP BY clause is, to divide the rows in a table into smaller groups.
- The GROUP BY clause is used with the SQL SELECT statement.
- The grouping can happen after retrieves the rows from a table.
- When some rows are retrieved from a grouped result against some condition, that is possible with HAVING clause.
- The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group.
- The WHERE clause is used to retrieve rows based on a certain condition, but it cannot be applied to grouped result.
- In an SQL statement, suppose you are using GROUP BY, if required you can use HAVING instead of WHERE, after GROUP BY.
Syntax:
SELECT <column_list>
FROM < table name >
WHERE <condition>GROUP BY <columns>
[HAVING] <condition>;
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 |
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 1: GROUP BY a single column
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 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 2: GROUP BY multiple columns
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 |
SQL Group by Example 1
SQL Group by Example 2
