Grouping Queries: GROUP BY and HAVING

A Grouping Query is a special type of query that groups and summarizes rows: SELECT * FROM table WHERE condition GROUP BY column [, ...] A Grouping Query groups rows based on common values in a set of grouping columns.
Rows with the same values for the grouping columns are placed in distinct groups.
Each group is treated as a single row in the query result. > SELECT semester, diploma FROM stud GROUP BY semester, diploma; semester | diploma ----------+--------- 1 | bio 2 | bio 3 | phy (3 rows) Once a table is grouped, columns that are not used in the grouping cannot be referenced (except in aggregate expressions) since a specific value in those columns is ambiguous - which row in the group should it come from?

GROUP BY and Aggregate Functions

GROUP BY clauses is very useful in combination with aggregate functions.

Aggregate functions (like SUM, AVG) return the aggregate of all column values every time they are called. Using GROUP BY it is possible to find the function value for each individual group of column values.

For example, to get the average semester grouped by diploma:

> SELECT diploma, avg(semester) FROM stud GROUP BY diploma; diploma | avg ---------+-------------- bio | 1.6666666667 phy | 3.0000000000

HAVING

The WHERE clause cannot be used against aggregate functions, HAVING has to be used instead: SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression Example: > SELECT diploma, avg(semester) FROM stud GROUP BY diploma HAVING avg(semester)>2; diploma | avg ---------+-------------- phy | 3.0000000000 (1 row)