Product Documentation

Appearance

GROUP BY Clause ​

Use the GROUP BY clause in conjunction with aggregate functions in the SELECT list. Name the columns that are grouped in order to calculate aggregate values such as counts, averages, and sums. You can also specify GROUP BY extensions: GROUPING SETS, ROLLUP, and CUBE.

[ GROUP BY { expression [, ...] |
GROUPING SETS (expression [, ...]) |
ROLLUP (expression [, ...]) |
CUBE (expression [, ...]) 
} ]

When an aggregate function is used in the SELECT list, all non-aggregate columns or expressions in the SELECT list must be specified as grouping columns in the GROUP BY clause.

Grouping columns can be specified with their original names, SELECT list aliases, or ordinal numbers. You cannot use parameters defined in prepared statements, and you cannot use the result of a subquery as a grouping column. Subqueries in the GROUP BY clause return a syntax error.

Examples ​

Use the COUNT function to find out how many times each team has won the league:

premdb=# select winners, count(winners) 
from season 
where winners is not null 
group by winners;
     winners      | count 
-------------------+-------
 Manchester City   |     2
 Arsenal           |     3
 Chelsea           |     4
 Blackburn Rovers  |     1
 Leicester City    |     1
 Manchester United |    13
(6 rows)

You can also use an ordinal number to specify a grouping column:

premdb=# select winners, count(winners) from season 
where winners is not null 
group by 1;
     winners      | count 
-------------------+-------
 Manchester City   |     2
 Arsenal           |     3
 Chelsea           |     4
 Blackburn Rovers  |     1
 Leicester City    |     1
 Manchester United |    13
(6 rows)

You can also use a SELECT list alias to specify a grouping column:

premdb=# select winners team_name, count(winners) timeswon 
from season 
where winners is not null 
group by team_name;
    team_name     | timeswon 
-------------------+----------
 Manchester City   |        2
 Arsenal           |        3
 Chelsea           |        4
 Blackburn Rovers  |        1
 Leicester City    |        1
 Manchester United |       13
(6 rows)

Note that the WHERE clause does not accept SELECT list aliases.

In This Section

Parent topic:SELECT