GROUP BY Clause
The GROUP BY clause combines the results for identical values in a column or expression. This clause is typically used in conjunction with aggregate functions to generate a single figure for each unique value in a column or expression. For example, to obtain the number of orders for each part number in the orders table:
SELECT partno, count(*) FROM orders
GROUP BY partno;
The preceding query returns one row for each part number in the orders table, even though there can be many orders for the same part number.
Nulls are used to represent unknown data, and two nulls are typically not considered to be equal in SQL comparisons. However, the GROUP BY clause treats nulls as equal and returns a single row for nulls in a grouped column or expression.
Grouping can be performed on multiple columns or expressions. For example, to display the number of orders for each part placed each day:
SELECT odate, partno, count(*) FROM orders
GROUP BY odate, partno;
If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function. These columns can either be the column, an expression, or the ordinal number in the column list.
For example:
SELECT customerno,
CURRENT_DATE - odate AS days_since_order_placed,
COUNT(*) AS number_of_orders
FROM orders
GROUP BY customerno,
CURRENT_DATE - odate
ORDER BY 1, 2;
SELECT customerno,
CURRENT_DATE - odate AS days_since_order_placed,
COUNT(*) AS number_of_orders
FROM orders
GROUP BY customerno,
2
ORDER BY 1, 2;