DATABASE & SQL/PLSQL

Group clause summarize the result set in to a groups defined in the query using aggregate function.

SELECT  'max'=MAX (discount)
FROM  discountsSELECT column1, column2, ... column_n, aggregate_function (expression)
FROM table_name
WHERE conditions
GROUP BY ALL column1, column2, ... column_n

SELECT  'max'=MAX (discount)
FROM  discountsSELECT column1, column2, ... column_n, aggregate_function (expression)
FROM table_name
WHERE conditions
GROUP BY ALL column1, column2, ... column_n

expressions: describe the column name (s) or expressions on which the result set of the SELECT statement is to be grouped.

ALL is a keyword used to include those groups that do not meet the search condition.

Example

SELECT type, 'avg'=AVG(advance)
FROM titles
WHERE type LIKE 'b%'
GROUP BY ALL type

Output

Return all type from table but the 'avg' display only those type which started with ' b'. Means this keyword used to display all groups, including those exclude from WHERE clause. The ALL keyword is meaningful for those query that contain WHERE clause.

Example

SELECT  type,'pub Id'=pub_id, 'avg'=AVG (price)
FROM  titles
GROUP BY  type,pub_id

Return the 'type' and 'pub Id' and 'avg' , which calculated from titles table.

Example

SELECT type, 'avg'=AVG(advance)
FROM titles
WHERE title_id IN ('BU1032','PC1035')
GROUP BY ALL type

Output