SQL Group By Clause

The SQL GROUP By Clause is used in collaboration with the SELECT statement to arrange identical data into groups. Group by function establishes data groups based on columns and aggregates the information within a group only. The grouping criterion is defined by the columns specified in GROUP BY clause. Following this hierarchy, data is first organized in the groups and then WHERE clause restricts the rows in each group.

Syntax

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

Group By Syntax

Guidelines of using GROUP BY clause

• All the dependent columns or columns used in GROUP BY function must form the basis of grouping, hence must be included in GROUP BY clause also.
• GROUP BY clause does not support the use of column alias, but the actual names.
• GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN.
• The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
• The group by clause should contain all the columns in the select list expect those used along with the group functions.

Example

1. Display total orders for each salesman.

Example of Group By Clause

2. Display maximum order taken by each salesman with salesman number 1001 or 1003.

Example of Group By Clause with Where Condition

GROUP BY clause using The ROLLUP Operator

The ROLLUP Operator is used to calculate aggregates and super aggregates for expressions or columns within a GROUP BY clause. Generally, it is used to extract statistics and/or summaries from result set.

The ROLLUP operator first calculates the standard aggregate values for the group specified in the GROUP BY clause then creates higher level subtotals, moving from right to left through the list of grouping columns.

Example

Display total number of orders for each salesman for each day.

Example of Group By With ROLLUP

GROUP BY clause using The CUBE Operator

The CUBE Operator can be applied to all aggregate functions within a GROUP BY statement. It is used to extract tabular result from result set. CUBE produces subtotals for all possible combinations of grouping specified in the GROUP BY clause along with a grand total as against the ROLLUP operator which produce only a fraction of possible subtotal combinations.

The CUBE operator first calculates the standard aggregate values for the group specified in the GROUP BY clause then creates higher level subtotals, moving from right to left through the list of grouping columns. Additionally the CUBE operator displays the total irrespective of columns of CUBE Expression.

Example

Display total of orders for each salesman for each day along with grand total of salesman 1001,1002 and 1003.

Example of Group By with CUBE

Self Exercise

1. Find out each customer’s smallest order.
2. Count the no. of salesmen registering orders for each day.
3. Calculate the total of orders for each day for each customer along with subtotals and grand
   totals.
4. List total number of salesmen for each city.