GROUP BY clause is used to divide a table into logical groups and calculate aggregate statistics for each group.
Important categories of GROUP BY clause are as below.
- GROUP BY clause appears after the WHERE clause and before the ORDERBY clause.
- We can group columns or derived columns.
- Columns from the input table can appear in an aggregate query's SELECT clause only when they are also included in the GROUP BY clause.
- Group BY expression must match the SELECT expression exactly.
- If you specify multiple grouping columns in the GROUP BY clause to nest groups, data is summarized at the final specified group.
- If WHERE clause is used in the query containing a GROUP BY clause, Rows are eliminate first which dose not satisfy where condition and then grouping ocuurs.
- You can not use column alias in the GROUP BY clause but table aliases are allowed.
Syntax of GROUP BY Clause :
SELECT columns
FROM table
[WHERE search_condition]
GROUP BY grouping_columns
[HAVING search_condition]
[ORDER BY sort_columns]
columns and grouping columns are one or more comma separated column names.
table is a name of table that contains columns and grouping_columns.
search_condition is a valid sql expression.
sort_columns are one or more column name. of specified table.
Examples of GROUP BY Clause :
Example 1 : Use of GROUP BY clause in select clause
SELECT OrderID, COUNT(ProductID) AS NumberOfOrders
FROM Order_Details
GROUP BY OrderID
Output
OrderID NumberOfOrders
10248 3
10249 2
10250 3
10251 3
10252 3
10253 3
10254 3
10255 4
Above example counts the number of products ordered in particular order using group by clause. OrderID is called the grouping column.
Example 2 : Difference between COUNT(expr) and COUNT(*) in a query that contains GROUP BY clause
SELECT Region, COUNT(Region) AS TotalRegion, COUNT(*) AS TotalRows
FROM Invoices
GROUP BY Region
Output
Region TotalRegion TotalRows
AK 24 24
DF 4 4
SP 120 120
WA 51 51
NULL 0 1329
As above example illustrates that COUNT(Region) counts only non-null values and COUNT(*) counts all values including nullnulls. In the result GROUP BY recognizes the null and creates a null group for it.