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).