Beyond simple column grouping

Every relational database supports GROUP BY, but PostgreSQL lets you do more with it than most people use. Beyond grouping by plain columns, you can group by arbitrary expressions, use subqueries in the grouping criteria, and combine multiple aggregations in one pass. Here's what that looks like in practice.

To demonstrate, consider a table tracking oil production and consumption by country over time:

1

2

3

4

5

6

7

8

test=# CREATE TABLE t_oil (

          region       text,

          country      text,

          year         int,

          production   int,

          consumption  int

);

CREATE TABLE

You can load this data directly from the web using COPY ... FROM PROGRAM, which requires superuser privileges. In the example, 644 rows are loaded.

1

2

3

test=# COPY t_oil FROM PROGRAM

          'curl /secret/oil_ext.txt';

COPY 644

The basics: column positions and names

You can group by a column position instead of a name:

1

2

3

4

5

6

test=# SELECT region, avg(production) FROM t_oil GROUP BY 1;

      region   |          avg

---------------+-----------------------

North America | 4541.3623188405797101

   Middle East | 1992.6036866359447005

(2 rows)

That query is exactly equivalent to grouping by the region column:

1

2

3

4

5

6

test=# SELECT region, avg(production) FROM t_oil GROUP BY region;

     region    |          avg

---------------+-----------------------

North America | 4541.3623188405797101

   Middle East | 1992.6036866359447005

(2 rows)

Whether you prefer positional or named grouping is purely a matter of style — the two forms are identical in behavior.

Grouping by calculated values

It's also possible to define groups on the fly using an expression. For example, you can split rows into buckets based on a threshold:

1

2

3

4

5

6

7

8

9

test=# SELECT production > 9000, count(production)

       FROM   t_oil

       WHERE  country = 'USA'

       GROUP BY production > 9000;

?column? | count

----------+-------

        f |   20

        t |   26

(2 rows)

This produces two groups: one where the condition evaluates to true (production above 9000) and one where it evaluates to false. You can use any expression as the grouping key. To count odd and even years:

1

2

3

4

5

6

7

8

9

test=# SELECT count(production)

       FROM     t_oil

       WHERE    country = 'USA'

       GROUP BY CASE WHEN year % 2 = 0 THEN true ELSE false END;

count

-------

23

23

(2 rows)

Note that the grouping expression doesn't have to appear in the SELECT list. You can also put a full subquery inside the GROUP BY clause. The query above is equivalent to:

1

2

3

4

5

6

7

8

test=# SELECT count(production)

       FROM   t_oil WHERE country = 'USA'

       GROUP BY (SELECT CASE WHEN year % 2 = 0 THEN true ELSE false END);

count

-------

23

23

(2 rows)

PostgreSQL estimates the number of groups accurately during planning, as seen in the execution plan for these queries:

1

2

3

4

5

6

7

8

9

10

11

12

13

test=# explain SELECT count(production)

       FROM    t_oil

       WHERE   country = 'USA'

       GROUP BY (SELECT CASE WHEN year % 2 = 0 THEN true ELSE false END);

                           QUERY PLAN

-------------------------------------------------------------

HashAggregate (cost=14.97..15.02 rows=2 width=9)

   Group Key: (SubPlan 1)

   -> Seq Scan on t_oil (cost=0.00..14.74 rows=46 width=5)

      Filter: (country = 'USA'::text)

      SubPlan 1

      -> Result (cost=0.00..0.01 rows=1 width=1)

(6 rows)

The planner correctly expects two groups.

HAVING: aliases not allowed, expressions required

A common question is whether you can reference a column alias in a HAVING clause:

1

2

3

4

5

6

7

test=# SELECT count(production) AS x

       FROM   t_oil

       WHERE country = 'USA'

       GROUP BY year < 1990 HAVING x > 22;

ERROR: column 'x' does not exist

LINE 1: ...il WHERE country = 'USA' GROUP BY year < 1990 HAVING x > 22;

^

The answer is no — SQL doesn't permit that. If you need to filter on an aggregate, you have to spell out the full expression in HAVING:

1

2

3

4

5

6

7

8

test=# SELECT count(production) AS x

       FROM   t_oil

       WHERE country = 'USA'

       GROUP BY year < 1990 HAVING count(production) > 22;

x

----

25

(1 row)

That doesn't mean the SELECT and HAVING clauses must reference the same aggregate. You can mix them freely:

1

2

3

4

5

6

7

8

9

test=# SELECT count(production) AS x

       FROM   t_oil

       WHERE  country = 'USA'

       GROUP BY year < 1990 HAVING avg(production) > 0;

x

----

21

25

(2 rows)

Here, count is in the select list while avg is used for filtering — this is valid and often handy.

Multiple aggregations with grouping sets

Grouping sets let you run several aggregation levels in a single query, which can speed things up compared to running separate queries. The following produces one group for all rows and one each for pre- and post-1990 data:

1

2

3

4

5

6

7

8

9

10

test=# SELECT year < 1990, count(production) AS x

       FROM   t_oil

       WHERE  country = 'USA'

       GROUP BY GROUPING SETS ((year < 1990), ());

?column? | x

----------+----

          | 46

        f | 21

        t | 25

(3 rows)

If you find that syntax cumbersome, ROLLUP is equivalent and adds an extra row that aggregates everything:

1

2

3

4

5

6

7

8

9

10

test=# SELECT year < 1990, count(production) AS x

       FROM   t_oil

       WHERE  country = 'USA'

       GROUP BY ROLLUP (year < 1990);

?column? | x

----------+----

          | 46

        f | 21

        t | 25

(3 rows)