SQL: GROUPING SETS, CASE

created:

updated:

tags: database

GROUPING SETS

A grouping set is a set of columns by which you group by using the GROUP BY clause. A grouping set is denoted by a comma-separated list of columns placed inside parantheses (column1, column2, …)

Example of using grouping set

SELECT brand, segment, SUM (quantity)
FROM sales
GROUP BY brand, segment;
  • It has a grouping set of the brand and segment ((brand, segment))

GROUPING SETS

PostgreSQL has a performance issue when a table is scanned separately for multiple times. To eliviate this issue, we can use GROUPING SETS which is the subclause of the GROUP BY clause.

The GROUPING SETS allows you to define multiple grouping sets in the same query.

# It has 4 grouping sets
SELECT c1, c2, aggregate_function(c3)
FROM table_name
GROUP BY
  GROUPING SETS (
    (c1, c2),
    (c1),
    (c2),
    ()
  )

This is equivalent to the following:

SELECT c1, c2, aggregate_functions(c3)
FROM table_name
GROUP BY c1, c2

UNION ALL

SELECT c1, NULL, aggregate_function(c3)
FROM table_name
GROUP BY c1

UNION ALL

SELECT NULL, c2, aggregate_function(c3)
FROM table_name
GROUP BY c2

UNION ALL

SELECT NULL, NULL, SUM(c3)
FROM sales;

The query with GROUPING SETS is much shorter and more readable. Not only that, PostgreSQL will optimize the number times it scans the table.

GROUPING function

CASE

The CASE expression is like if/else statements, it allows us to have if-else logic to the query. CASE can be used anywhere such as SELECT, WHERE, GROUP BY, HAVING, etc.

General CASE

SELECT title,
       length,
       CASE 
           # if length is less than 50 minutes, the film is short
           WHEN length > 0
                AND length <= 50 THEN 'Short'
           # if length is greater than 50 minutes and less than or equal to 120 minutes, the film is medium
           WHEN length > 50
                AND length <= 120 THEN 'Medium'
           # if length is greater than 120 minutes, the film is long
           WHEN length > 120 THEN 'Long'
       END duration
FROM film
ORDER BY title;
  • We can also use CASE expression with aggreagate function
# This shows the number of films that belong to economy, mass, and premium
SELECT
  SUM (CASE
           WHEN rental_rate = 0.99 THEN 1
           ELSE 0
       END    
  ) AS "Economy",
  SUM (CASE
           WHEN rental_rate = 2.99 THEN 1
           ELSE 0
       END   
  ) AS "Mass",
  SUM (CASE
           WHEN rental_rate = 4.99 THEN 1
           ELSE 0
       END
  ) AS "Premium"
FROM film;

Simple CASE expression

SELECT title,
       rating
       CASE rating
           WHEN 'G' THEN 'General Audiences'
           WHEN 'PG' THEN 'Parental Guidance Suggested'
           WHEN 'PG-13' THEN 'Parents Strongly Cautioned'
           WHEN 'R' THEN 'Restricted'
           WHEN 'NC-17' THEN 'Adults Only'
       END rating_description
FROM film
ORDER BY title;

Example of simple expression with aggreate function

SELECT
  SUM(CASE rating
        WHEN 'G' THEN 1
                 ELSE 0
      END) "General Audiences",
  SUM(CASE rating
        WHEN 'PG' THEN 1
                  ELSE 0
      END) "Parental Guideance Suggested",
  SUM(CASE rating
        WHEN 'PG-13' THEN 1
                     ELSE 0
      END) "Parents Strongly Cautioned",
  SUM(CASE rating
        WHEN 'R' THEN 1
                 ELSE 0
      END) "Restricted",
  SUM(CASE rating
        WHEN 'NC-17' THEN 1
                     ELSE 0
      END) "Adults Only"
FROM film;

References


Copyright (c) 2025 Sunmin Kim