GROUP BY

GROUP BY is used when multiple columns from one or more tables are selected and at least one aggregate function appears in the SELECT statement. In that case, you need to GROUP BY all the selected columns except the ones operated on by the aggregate function.

All data types (including: BLOB, CLOB, and XML) are supported by GROUP BY.

Syntax

SELECT column1, ... column_n, aggregate_function (expression)

FROM table

GROUP BY column1, ... column_n;

Example (GROUP BY with Multiple Inner Joins)

SELECT orderdetails.Status, count (orderdetails.Status) as Item_Count

FROM /shared/examples/ds_orders/orderdetails Orderdetails 

INNER JOIN /shared/examples/ds_inventory/products Products 

ON orderdetails.ProductID = products.ProductID 

INNER JOIN /shared/examples/ds_orders/orders Orders 

ON orders.OrderID = orderdetails.OrderID 

GROUP BY orderdetails.Status

Example (GROUP BY with Columns Specified by Ordinal Position)

Columns that are to be used for grouping can be defined by the integer that represents the ordinal position in which the SELECT occurred. If all columns of a table are selected (SELECT *), you can use the column position in the table (expressed as an integer).

SELECT ProductId, UnitsSold, UnitPrice

FROM /shared/examples/ds_inventory/inventorytransactions InventoryTransactions

GROUP BY 2 DESC, 1, 3

This sample query selects the three columns ProductId, UnitsSold, and UnitPrice from the inventorytransactions table and groups the results first by UnitsSold (in descending order), then by ProductId (in ascending order), and then by UnitPrice (in ascending order).