The GROUP BY
clause enables you to establish data groups based on columns. The grouping criterion is defined by the GROUP BY
clause, which is followed by the
WHERE
clause in the SQL execution path. Following this execution path, the result set rows are grouped based on like values of grouping columns and the
WHERE
clause restricts the entries in each group.
Note
All columns that are used besides the aggregate functions must be included in the GROUP BY
clause. The GROUP BY
clause does not support the use of column aliases; you must use the actual column names. The GROUP BY
columns may or may not appear in the
SELECT
list. The GROUP BY
clause can only be used with aggregate functions such as
SUM
,
AVG
,
COUNT
,
MAX
, and
MIN
.
The following statement illustrates the syntax of the GROUP BY
clause:
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;
The expression1, expression2, ... expression_n
commands are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY
clause.
Let's take a look at these commands:
aggregate_function
: This performs many functions, such asSUM
(http://www.techonthenet.com/oracle/functions/sum.php),COUNT
(http://www.techonthenet.com/oracle/functions/count.php),MIN
(http://www.techonthenet.com/oracle/functions/min.php),MAX
(http://www.techonthenet.com/oracle/functions/max.php), orAVG
(http://www.techonthenet.com/oracle/functions/avg.php).tables
: This is where you can retrieve records from. There must be at least one table listed in theFROM
clause.conditions
: This is a condition that must be met for the records to be selected.
The GROUP BY
clause must appear right after the FROM
or WHERE
clause. Followed by the GROUP BY
clause is one column or a list of comma-separated columns. You can also put an expression in the GROUP BY
clause.
As mentioned in the previous paragraph, the GROUP BY
clause divides rows returned from the
SELECT
statement into groups. For each group, you can apply an aggregate function, for example, to calculate the sum of items or count the number of items in the groups.
Let's look at a GROUP BY
query example that uses the SUM
function (http://www.techonthenet.com/oracle/functions/sum.php). This example uses the SUM
function to return the name of the product and the total sales (for the product).
SELECT product, SUM(sale) AS "Total sales" FROM order_details GROUP BY product;
In the select statement, we have sales where we applied the SUM
function and the other field product is not part of SUM
, we must use in the GROUP BY
clause.