Grouping Rows with GROUP BY clause

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.