GROUP BY clause

The GROUP BY <Grouping fields> clause is used to describe the query results grouping order.

Initial data in the query can be grouped using aggregate functions specified as fields in the selection list. This means that query result rows will contain calculation results of the specified aggregate functions, calculated (grouped) by source table record.

Aggregate functions are specified in the selection fields list; in the GROUP BY <Grouping fields> clause you must indicate a list of fields used for grouping. Source table records containing similar values for specified fields will be grouped in the query.

You can use logical expressions in the clause.

The grouping fields list contains references to source table fields (query sources), comma-separated:

GROUP BY <Grouping fields>
	|
	<Field dereferencing> [, <Field dereferencing> [, …]]

Important! When you group query results, the selection fields list should contain aggregate functions; apart from aggregate functions the selection fields list can only contain fields used for grouping.
The only exception is when aggregate functions are applied to nested table fields. In this case, you can access top-level table fields in the selection fields list, without grouping the results by these fields.

When using aggregate functions, you can omit the GROUP BY clause; in this case all query results are grouped into a single row.

Example:

// You need to obtain statistics on the sales of goods:
// maximum, minimum and average prices in invoices.

SELECT
   Invoice.Item,
   AVG (Invoice.Price) AS Average,
   MAX (Invoice.Price) AS Maximum,
   MIN (Invoice.Price) AS Minimum

FROM
   Document.Invoice.Content AS Invoice

GROUP BY
   Invoice.Item

Query result:

Item	Average	Maximum	Minimum
Children’s pants	1 500	1 500	1 500
"Cowboy" shirt	800	800	800
Women’s jeans	1 520	1 700	1 500
Children’s sweater	800	800	800
"Lilia" sink	2 250	2 500	2 000
"Aquarium" bathroom	3 166	4 000	2 000
"Ultra" mixer	1 500	1 500	1 500
"Krups" food processor	1 500	1 500	1 500