PostgreSQL Aggregate Functions and Grouping

1. What You'll Learn

The five core aggregate functions: COUNT / SUM / AVG / MAX / MIN
GROUP BY for single-column and multi-column grouping
HAVING clause for filtering grouped results
PostgreSQL features: GROUPING SETS / ROLLUP / CUBE multi-dimensional grouping
PostgreSQL feature: FILTER clause for conditional aggregation
Using DISTINCT within aggregates
How aggregate functions handle NULL

2. The Story

Bob is a data analyst at a cross-border e-commerce platform. As the year-end approaches, the CEO asks him to produce an annual sales analysis report:

Count orders and sales revenue per region
Cross-analyze across multiple dimensions (quarter + region)
Compute the revenue difference between months with and without orders
Produce summary data for all dimensions in one go

Bob finds that a plain GROUP BY can only group by one dimension at a time, forcing him to write multiple SQL statements and UNION them. That is, until he learns PostgreSQL's GROUPING SETS and FILTER clauses, which let a single SQL statement meet every requirement.

3. Concept

(1) Aggregate Function Overview

Aggregate functions collapse multiple input rows into a single output row and are the cornerstone of data analysis.

SQL

SELECT
  COUNT(*)        AS total_rows,
  COUNT(amount)   AS non_null_count,
  SUM(amount)     AS total_amount,
  AVG(amount)     AS avg_amount,
  MAX(amount)     AS max_amount,
  MIN(amount)     AS min_amount
FROM orders;

TEXT

 total_rows | non_null_count | total_amount | avg_amount | max_amount | min_amount
------------+----------------+--------------+------------+------------+------------
        100 |             95 |     12500000 |  131578.95 |     500000 |       1200

(2) The Five Aggregate Functions in Detail

Function	Purpose	NULL behavior	Return type
`COUNT(*)`	Count all rows (incl. NULL)	Includes NULL	bigint
`COUNT(col)`	Count non-NULL rows	Ignores NULL	bigint
`SUM(col)`	Sum	Ignores NULL; all-NULL returns NULL	Same as input
`AVG(col)`	Average	Ignores NULL	numeric
`MAX(col)` / `MIN(col)`	Max / min value	Ignores NULL	Same as input

▶ Example: COUNT(*) vs COUNT(col)

SQL

SELECT
  COUNT(*)       AS all_rows,
  COUNT(discount) AS rows_with_discount
FROM orders;

TEXT

 all_rows | rows_with_discount
----------+-------------------
      100 |                 42

58 rows have a NULL discount, so COUNT(discount) excludes them.

▶ Example: SUM and AVG NULL Handling

SQL

SELECT
  SUM(discount)  AS total_discount,
  AVG(discount)  AS avg_discount
FROM orders
WHERE region = 'NA';

TEXT

 total_discount |     avg_discount
----------------+--------------------
         125000 | 2976.1904761904762

AVG averages only the non-NULL rows: 125000 / 42 ≈ 2976.19, not 125000 / 100.

▶ Example: MAX/MIN for Extremes

SQL

SELECT
  MAX(created_at) AS latest_order,
  MIN(created_at) AS earliest_order
FROM orders;

TEXT

     latest_order      |    earliest_order
------------------------+------------------------
 2025-12-28 15:30:00   | 2025-01-03 09:12:00

▶ Example: Aggregating an Empty Result Set

SQL

SELECT
  COUNT(*)  AS cnt,
  SUM(amount) AS total
FROM orders
WHERE region = 'ANTARCTICA';

TEXT

 cnt | total
-----+-------
   0 |

COUNT returns 0 for an empty set, while SUM returns NULL for an empty set—a classic pitfall.

(3) GROUP BY Grouping

GROUP BY groups rows by the specified column(s), producing one aggregate row per group.

SQL

SELECT
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region;

TEXT

 region | order_count | total_amount
--------+-------------+--------------
 EU     |          35 |      4200000
 NA     |          45 |      5800000
 APAC   |          20 |      2500000

▶ Example: GROUP BY Multiple Columns

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region, EXTRACT(QUARTER FROM created_at)
ORDER BY region, quarter;

TEXT

 region | quarter | order_count | total_amount
--------+---------+-------------+--------------
 APAC   |       1 |           5 |       620000
 APAC   |       2 |           6 |       780000
 APAC   |       3 |           4 |       500000
 APAC   |       4 |           5 |       600000
 EU     |       1 |           8 |       950000
 EU     |       2 |           9 |      1100000
 ...

(4) HAVING Filtering Groups

WHERE filters rows before grouping; HAVING filters groups after grouping.

Clause	When applied	Allows aggregates
WHERE	Before GROUP BY	No
HAVING	After GROUP BY	Yes

▶ Example: HAVING Filter for High-Revenue Regions

SQL

SELECT
  region,
  SUM(amount) AS total_amount
FROM orders
GROUP BY region
HAVING SUM(amount) > 3000000
ORDER BY total_amount DESC;

TEXT

 region | total_amount
--------+--------------
 NA     |      5800000
 EU     |      4200000

The APAC total of 2,500,000 is filtered out.

▶ Example: WHERE + HAVING Combination

SQL

SELECT
  region,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE amount >= 5000
GROUP BY region
HAVING COUNT(*) >= 10
ORDER BY total_amount DESC;

TEXT

 count 
-------
     5
(1 row)

First the orders below 5,000 are filtered out, then regions with fewer than 10 orders in the group are removed.

4. Key Points

(1) Using DISTINCT within Aggregates

▶ Example: Counting Distinct Customers

SQL

SELECT
  COUNT(DISTINCT customer_id) AS unique_customers,
  COUNT(*)                    AS total_orders
FROM orders;

TEXT

 unique_customers | total_orders
------------------+--------------
              780 |          1000

▶ Example: SUM DISTINCT to Avoid Double Counting

SQL

SELECT
  SUM(DISTINCT bonus) AS unique_bonus_total
FROM employee_targets;

TEXT

  result  
----------
   42.50
(1 row)

(2) FILTER Clause (PostgreSQL Feature)

The FILTER clause lets you aggregate the same set of rows under different conditions without writing multiple CASE WHEN expressions.

SQL

SELECT
  region,
  COUNT(*) FILTER (WHERE amount >= 10000)  AS high_value_orders,
  COUNT(*) FILTER (WHERE amount < 10000)   AS low_value_orders,
  SUM(amount) FILTER (WHERE quarter = 1)   AS q1_revenue,
  SUM(amount) FILTER (WHERE quarter = 2)   AS q2_revenue
FROM orders
GROUP BY region;

Approach	Syntax	Readability	Performance
CASE WHEN	`SUM(CASE WHEN ... THEN x ELSE 0 END)`	Fair	One scan
FILTER	`SUM(x) FILTER (WHERE ...)`	Excellent	One scan

▶ Example: FILTER for Months With/Without Orders

SQL

SELECT
  region,
  COUNT(DISTINCT EXTRACT(MONTH FROM created_at))
    FILTER (WHERE amount > 0)  AS months_with_orders,
  12 - COUNT(DISTINCT EXTRACT(MONTH FROM created_at))
    FILTER (WHERE amount > 0) AS months_without_orders
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY region;

TEXT

 count 
-------
     5
(1 row)

▶ Example: FILTER for Year-over-Year Comparison

SQL

SELECT
  region,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024) AS revenue_2024,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025) AS revenue_2025
FROM orders
GROUP BY region;

TEXT

  result  
----------
   42.50
(1 row)

(3) GROUPING SETS / ROLLUP / CUBE (PostgreSQL Feature)

Produces aggregate results across multiple dimensions in a single query, with no need to write multiple SQL statements and UNION them.

▶ Example: GROUPING SETS with Custom Dimension Combinations

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount
FROM orders
GROUP BY GROUPING SETS (
  (region, EXTRACT(QUARTER FROM created_at)),
  (region),
  (EXTRACT(QUARTER FROM created_at)),
  ()
)
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT

 region | quarter | total_amount
--------+---------+--------------
 APAC   |       1 |       620000
 APAC   |       2 |       780000
 APAC   |       3 |       500000
 APAC   |       4 |       600000
 APAC   |         |      2500000
 EU     |       1 |       950000
 ...
        |       1 |      2200000
 ...
        |         |     12500000

NULL means that dimension is at the summary level; use the GROUPING() function to tell real NULLs apart from summary-level NULLs.

▶ Example: ROLLUP Hierarchical Summary

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount,
  GROUPING(region) AS g_region,
  GROUPING(quarter) AS g_quarter
FROM orders
GROUP BY ROLLUP (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT

  result  
----------
   42.50
(1 row)

Syntax	Equivalent GROUPING SETS	Output dimensions
`ROLLUP(a, b)`	`(a,b), (a), ()`	Hierarchy: detail → subtotal → grand total
`CUBE(a, b)`	`(a,b), (a), (b), ()`	Full cross: all combinations
`GROUPING SETS((a),(b))`	`(a), (b)`	Any custom combination

▶ Example: CUBE Full-Dimension Cross

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount) AS total_amount
FROM orders
GROUP BY CUBE (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT

  result  
----------
   42.50
(1 row)

(4) NULL Behavior of Aggregate Functions Summary

Scenario	COUNT(*)	COUNT(col)	SUM	AVG	MAX/MIN
Has non-NULL values	Counts all rows	Counts non-NULL only	Sums ignoring NULL	Averages ignoring NULL	Extremes ignoring NULL
All NULL	Counts rows	0	NULL	NULL	NULL
Empty result set	0	0	NULL	NULL	NULL

5. Practice

▶ Example: Top 3 Sales by Product Category

SQL

SELECT
  category,
  SUM(amount) AS total_amount
FROM orders
GROUP BY category
ORDER BY total_amount DESC
LIMIT 3;

TEXT

  result  
----------
   42.50
(1 row)

▶ Example: Customer Retention Rate per Region

SQL

SELECT
  region,
  COUNT(DISTINCT customer_id) FILTER (
    WHERE EXTRACT(YEAR FROM created_at) = 2024
  ) AS customers_2024,
  COUNT(DISTINCT customer_id) FILTER (
    WHERE EXTRACT(YEAR FROM created_at) = 2025
  ) AS customers_2025,
  ROUND(
    COUNT(DISTINCT customer_id) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025)::numeric
    / NULLIF(
      COUNT(DISTINCT customer_id) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024),
      0
    ) * 100, 1
  ) AS retention_rate
FROM orders
GROUP BY region;

TEXT

 count 
-------
     5
(1 row)

▶ Example: Monthly Year-over-Year Trend

SQL

SELECT
  EXTRACT(MONTH FROM created_at)::int AS month_num,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2024) AS revenue_2024,
  SUM(amount) FILTER (WHERE EXTRACT(YEAR FROM created_at) = 2025) AS revenue_2025
FROM orders
GROUP BY EXTRACT(MONTH FROM created_at)
ORDER BY month_num;

TEXT

  result  
----------
   42.50
(1 row)

▶ Example: Multi-Dimension Sales Report (ROLLUP + FILTER)

SQL

SELECT
  region,
  category,
  SUM(amount) AS total_amount,
  COUNT(*) FILTER (WHERE amount >= 50000) AS big_deals,
  GROUPING(region)  AS g_region,
  GROUPING(category) AS g_category
FROM orders
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

TEXT

 count 
-------
     5
(1 row)

▶ Example: Filtering High-Frequency Customers after Grouping

SQL

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5 AND SUM(amount) >= 100000
ORDER BY total_spent DESC;

TEXT

 count 
-------
     5
(1 row)

6. Comprehensive Example

Bob's annual sales analysis—one SQL statement producing a full-dimension report:

SQL

SELECT
  region,
  EXTRACT(QUARTER FROM created_at)::int AS quarter,
  SUM(amount)                              AS total_revenue,
  COUNT(*)                                 AS order_count,
  COUNT(DISTINCT customer_id)              AS unique_customers,
  SUM(amount) FILTER (WHERE amount >= 50000) AS big_deal_revenue,
  COUNT(*)  FILTER (WHERE amount >= 50000)   AS big_deal_count,
  AVG(amount)                              AS avg_order_value,
  GROUPING(region)  AS g_region,
  GROUPING(quarter) AS g_quarter
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025
GROUP BY ROLLUP (region, EXTRACT(QUARTER FROM created_at))
ORDER BY region NULLS LAST, quarter NULLS LAST;

TEXT

 region | quarter | total_revenue | order_count | unique_customers | big_deal_revenue | big_deal_count | avg_order_value | g_region | g_quarter
--------+---------+---------------+-------------+------------------+------------------+----------------+-----------------+----------+-----------
 APAC   |       1 |        620000 |           5 |                4 |          120000  |              1 |     124000.00   |        0 |         0
 APAC   |       2 |        780000 |           6 |                5 |          250000  |              2 |     130000.00   |        0 |         0
 APAC   |       3 |        500000 |           4 |                3 |           50000  |              1 |     125000.00   |        0 |         0
 APAC   |       4 |        600000 |           5 |                4 |          100000  |              1 |     120000.00   |        0 |         0
 APAC   |         |       2500000 |          20 |               12 |          520000  |              5 |     125000.00   |        0 |         1
 EU     |       1 |        950000 |           8 |                7 |          350000  |              3 |     118750.00   |        0 |         0
 ...
        |         |      12500000 |         100 |               780 |         5000000  |             45 |     125000.00   |        1 |         1

7. Execution Flow

The complete execution order of an aggregate query:

flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["Aggregate Functions<br/>COUNT/SUM/AVG/MAX/MIN"]
    E --> F[SELECT]
    F --> G[ORDER BY]
    G --> H[LIMIT]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style D fill:#fff9c4
    style E fill:#c8e6c9

Step	Clause	Description
1	FROM	Determine the data source
2	WHERE	Filter rows (before grouping)
3	GROUP BY	Group rows
4	Aggregate functions	Compute aggregate for each group
5	HAVING	Filter groups (after grouping)
6	SELECT	Select output columns
7	ORDER BY	Sort
8	LIMIT	Limit the number of rows

❓ FAQ

Q Is there a difference between COUNT(*) and COUNT(1)?

A They are completely equivalent in PostgreSQL. COUNT(*) is the recommended form, as its meaning is clearer.

Q Why does SUM return NULL instead of 0 for an empty group?

A The SQL standard states that SUM over all-NULL or empty input returns NULL. If you need 0, use COALESCE(SUM(col), 0).

Q Can HAVING be used without an aggregate function?

A Yes—HAVING region = 'NA' is syntactically valid, but such a condition belongs in WHERE for better performance.

Q Is the FILTER clause as fast as CASE WHEN?

A Essentially the same—both scan the data only once. FILTER reads more clearly and is the PostgreSQL-recommended form.

Q How does GROUPING SETS differ from multiple UNION'd SQL statements?

A GROUPING SETS scans the table only once, whereas multiple UNION'd SQL statements scan it several times. The performance gap is significant at large data volumes.

Q Can non-grouped columns appear in SELECT after GROUP BY?

A Not in PostgreSQL's strict mode. Any non-aggregated column in SELECT must also appear in GROUP BY, or it errors out.

📖 Summary

The five aggregate functions COUNT/SUM/AVG/MAX/MIN handle NULL differently
GROUP BY groups by column; HAVING filters grouped results
WHERE filters rows before grouping; HAVING filters groups after grouping
PostgreSQL's FILTER clause replaces CASE WHEN with cleaner syntax
GROUPING SETS / ROLLUP / CUBE produce multi-dimensional summaries in a single query
The GROUPING() function distinguishes real NULLs from summary-level NULLs
COUNT(*) returns 0 for an empty set; SUM/AVG return NULL

📝 Exercises

⭐ Count the number of orders and total amount for each region in the orders table, sorted by amount descending.
⭐ Find the customer_id values with more than 10 orders and an average amount above 50,000 USD.
⭐⭐ Using the FILTER clause, output each region's Q1–Q4 quarterly sales in a single SQL statement.
⭐⭐ Use CUBE to compute a full cross-dimensional summary over (region, category), and use GROUPING() to mark the summary rows.
⭐⭐⭐ Write a single SQL statement that outputs: the region summary, the category summary, the region+category detail, and the overall table total—scanning the orders table only once.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Aggregate Functions and Grouping

1. What You'll Learn

2. The Story

3. Concept

(1) Aggregate Function Overview

(2) The Five Aggregate Functions in Detail

▶ Example: COUNT(*) vs COUNT(col)

▶ Example: SUM and AVG NULL Handling

▶ Example: MAX/MIN for Extremes

▶ Example: Aggregating an Empty Result Set

(3) GROUP BY Grouping

▶ Example: GROUP BY Multiple Columns

(4) HAVING Filtering Groups

▶ Example: HAVING Filter for High-Revenue Regions

▶ Example: WHERE + HAVING Combination

4. Key Points

(1) Using DISTINCT within Aggregates

▶ Example: Counting Distinct Customers

▶ Example: SUM DISTINCT to Avoid Double Counting

(2) FILTER Clause (PostgreSQL Feature)

▶ Example: FILTER for Months With/Without Orders

▶ Example: FILTER for Year-over-Year Comparison

(3) GROUPING SETS / ROLLUP / CUBE (PostgreSQL Feature)

▶ Example: GROUPING SETS with Custom Dimension Combinations

▶ Example: ROLLUP Hierarchical Summary

▶ Example: CUBE Full-Dimension Cross

(4) NULL Behavior of Aggregate Functions Summary

5. Practice

▶ Example: Top 3 Sales by Product Category

▶ Example: Customer Retention Rate per Region

▶ Example: Monthly Year-over-Year Trend

▶ Example: Multi-Dimension Sales Report (ROLLUP + FILTER)

▶ Example: Filtering High-Frequency Customers after Grouping

6. Comprehensive Example

7. Execution Flow

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好