Offensive Security Researcher & Quantum Cryptography Analyst

How to Use Aggregate Functions in PostgreSQL

Abdul Wahab Junaid
How to Use Aggregate Functions in PostgreSQL

I remember the first real reporting query I had to write professionally — my manager wanted total revenue per month, broken down by product category. I stared at a raw sales table with a few hundred thousand rows and had no idea where to start. That’s when I really learned what aggregate functions are for. They’re not just academic SQL trivia — they’re the backbone of almost every dashboard, report, and analytics feature you’ll ever build.

In this article, I’ll walk you through PostgreSQL’s aggregate functions from the ground up: what they are, how to use each one, how they interact with GROUP BY and HAVING, and the mistakes that trip up almost everyone at some point.

What Are Aggregate Functions?

An aggregate function takes multiple rows of data and collapses them into a single summary value. Instead of returning every row, it returns one calculated result — a total, an average, a count, a minimum, or a maximum.

If I have a table of orders and I want to know the total revenue, I don’t want a list of every order’s price. I want one number. That’s exactly what aggregate functions give me.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    category VARCHAR(50),
    amount NUMERIC(10,2),
    order_date DATE
);

The Core Aggregate Functions

COUNT()

COUNT() tells you how many rows match a condition.

SELECT COUNT(*) FROM orders;

This counts every row in the table. If I only want to count non-null values in a specific column:

SELECT COUNT(customer_id) FROM orders;

Note the difference: COUNT(*) counts all rows regardless of NULLs, while COUNT(column_name) only counts rows where that column isn’t NULL.

To count distinct values:

SELECT COUNT(DISTINCT customer_id) FROM orders;

This tells me how many unique customers placed orders, not how many orders were placed.

SUM()

SUM() adds up numeric values.

SELECT SUM(amount) FROM orders;

This gives me total revenue across every order. I use this constantly for financial reporting.

AVG()

AVG() calculates the mean.

SELECT AVG(amount) FROM orders;

One thing worth knowing: AVG() on integer columns can return unexpected precision. If amount were an INT instead of NUMERIC, PostgreSQL still returns a numeric average (not a truncated integer), which is usually what you want, but it’s worth double-checking your data types.

MIN() and MAX()

These return the smallest and largest values in a column.

SELECT MIN(amount), MAX(amount) FROM orders;

These work on numbers, dates, and even text (alphabetical comparison), which makes them versatile:

SELECT MIN(order_date), MAX(order_date) FROM orders;

ARRAY_AGG()

This one is less commonly taught but incredibly useful. It collapses multiple rows into a single array.

SELECT customer_id, ARRAY_AGG(amount) AS all_amounts
FROM orders
GROUP BY customer_id;

I use this a lot when I need to see every value associated with a group without running a separate query per group.

STRING_AGG()

Similar to ARRAY_AGG(), but it concatenates text values with a separator:

SELECT customer_id, STRING_AGG(category, ', ') AS categories_ordered
FROM orders
GROUP BY customer_id;

This is perfect for generating a comma-separated summary — like “Electronics, Books, Clothing” — for each customer.

Using GROUP BY With Aggregate Functions

Aggregate functions become truly powerful when combined with GROUP BY, which splits your rows into groups before aggregating.

SELECT category, SUM(amount) AS total_revenue
FROM orders
GROUP BY category;

This gives me total revenue per category, not one overall total. Every column in your SELECT list that isn’t wrapped in an aggregate function must appear in the GROUP BY clause — this is a rule PostgreSQL enforces strictly, and it’s the single most common error beginners run into.

Multiple Grouping Columns

SELECT category, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_revenue
FROM orders
GROUP BY category, DATE_TRUNC('month', order_date)
ORDER BY month, category;

This is essentially the query I mentioned at the start of this article — monthly revenue broken down by category. DATE_TRUNC() rounds a timestamp down to the given precision (month, day, year, etc.), which makes it perfect for time-based reporting.

Filtering Groups With HAVING

Here’s a distinction that confuses a lot of beginners: WHERE filters rows before aggregation, while HAVING filters groups after aggregation.

SELECT category, SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 10000;

You can’t use an aggregate function inside a WHERE clause — PostgreSQL will throw an error, because at the point WHERE is evaluated, aggregation hasn’t happened yet. If you want to filter based on an aggregated value, HAVING is the only option.

You can combine both:

SELECT category, SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY category
HAVING SUM(amount) > 10000
ORDER BY total_revenue DESC;

Here, WHERE filters to only 2026 orders first, then groups by category, then filters out categories below the revenue threshold.

The FILTER Clause

PostgreSQL has a really elegant feature that a lot of other databases lack: the FILTER clause, which lets you apply a condition to a specific aggregate without needing a subquery or CASE statement.

SELECT
    category,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE amount > 100) AS large_orders
FROM orders
GROUP BY category;

This gives me total orders per category, alongside a count of only the “large” orders — in a single pass, no subqueries needed. I use FILTER constantly once I discovered it; it makes reporting queries dramatically cleaner than the equivalent CASE WHEN approach.

Combining Aggregates With CASE

Before I knew about FILTER, I used CASE statements inside aggregates, and it’s still a valid and common pattern:

SELECT
    category,
    SUM(CASE WHEN order_date >= '2026-01-01' THEN amount ELSE 0 END) AS revenue_2026,
    SUM(CASE WHEN order_date < '2026-01-01' THEN amount ELSE 0 END) AS revenue_before_2026
FROM orders
GROUP BY category;

This is a classic pattern for building pivot-table-style reports directly in SQL.

Aggregate Functions With NULL Values

This trips up a lot of people: aggregate functions generally ignore NULLs, except COUNT(*).

SELECT AVG(amount) FROM orders WHERE amount IS NOT NULL;

Actually, you don’t even need the WHERE clause here — AVG(), SUM(), MIN(), and MAX() all skip NULL values automatically when calculating. But if every value in the group is NULL, SUM() returns NULL (not zero), which can catch people off guard in financial reports. I usually wrap these in COALESCE() to force a zero default:

SELECT COALESCE(SUM(amount), 0) AS total_revenue FROM orders;

Window Functions vs Aggregate Functions

I want to briefly clarify something that confused me early on: regular aggregate functions collapse rows into one row per group. But sometimes you want the aggregate value alongside every individual row, not collapsed. That’s what window functions do:

SELECT
    id,
    category,
    amount,
    SUM(amount) OVER (PARTITION BY category) AS category_total
FROM orders;

This shows every order row individually, but adds a column showing the total for that order’s category. This is a different mechanism from GROUP BY aggregation — it’s worth knowing they exist, even though this article focuses on standard aggregate functions.

Statistical Aggregate Functions

Beyond the basics, PostgreSQL ships with a set of statistical aggregate functions that I’ve found genuinely useful once I started doing more analytical reporting work rather than just simple totals.

Standard deviation and variance:

SELECT
    category,
    AVG(amount) AS mean_order_value,
    STDDEV(amount) AS std_deviation,
    VARIANCE(amount) AS variance
FROM orders
GROUP BY category;

This is useful for spotting categories with wildly inconsistent order values versus ones that are very predictable — a high standard deviation relative to the mean often signals a mix of very different customer segments buying in the same category.

Percentile calculations:

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_order_value,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) AS p90_order_value
FROM orders;

PERCENTILE_CONT is what I reach for whenever someone asks for a median instead of an average — averages get skewed heavily by outliers (one huge enterprise order can distort your “typical” order value), while the median gives a much more honest picture of what a normal transaction looks like. I use p90/p95 calculations constantly for performance monitoring dashboards as well, not just financial data.

Mode (most frequent value):

SELECT MODE() WITHIN GROUP (ORDER BY category) AS most_common_category
FROM orders;

Aggregate Functions on Arrays and JSON

Since a lot of my own work involves JSONB columns, it’s worth mentioning that aggregate functions extend naturally into these types too.

SELECT customer_id, JSONB_AGG(amount) AS all_order_amounts
FROM orders
GROUP BY customer_id;

This builds a JSON array per group directly in SQL, which is genuinely handy when an API endpoint needs to return nested grouped data without a separate application-layer transformation step.

Performance Behavior of Aggregate Queries

It’s worth understanding, at a basic level, how PostgreSQL actually executes an aggregation. When you run a GROUP BY query, the planner chooses between two main strategies: a sort-based aggregate (sort all rows by the grouping columns, then walk through them summing as it goes) or a hash-based aggregate (build an in-memory hash table keyed by the grouping columns, updating running totals as rows stream in).

Hash aggregation is usually faster when there’s enough working memory (work_mem) to hold the hash table, and it doesn’t require the data to be pre-sorted. Sort-based aggregation becomes necessary when the number of distinct groups is too large to fit comfortably in memory, or when the data is already naturally ordered (say, by an index) so sorting is nearly free.

You can see which strategy PostgreSQL picked using EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT category, SUM(amount) FROM orders GROUP BY category;

If you notice a query using a disk-based sort (visible in the plan as a “Sort Method: external merge”) for an aggregation that should comfortably fit in memory, increasing work_mem for that session can often meaningfully speed things up:

SET work_mem = '64MB';

Common Use Cases

Aggregate Functions With DISTINCT

Every aggregate function in PostgreSQL can optionally be combined with DISTINCT to operate only on unique values before aggregating, not just COUNT.

SELECT
    SUM(DISTINCT amount) AS sum_of_unique_amounts,
    AVG(DISTINCT amount) AS avg_of_unique_amounts
FROM orders;

I want to flag this one carefully, because it’s rarely what people actually mean when they first try it. SUM(DISTINCT amount) doesn’t sum unique orders — it sums each unique dollar value exactly once, even if that value appears on ten different orders. If three separate orders each happen to be exactly $50.00, SUM(DISTINCT amount) counts that $50.00 figure only a single time, which is almost never the business question anyone is actually asking. I’ve seen this misused in financial reports where someone wanted “total revenue from unique customers” and instead got a number based on deduplicating identical dollar amounts, which silently produced a wrong total. If you genuinely need distinct-value aggregation, make sure you understand precisely what’s being deduplicated — the value, not the row — before shipping a report built on it.

Building a Full Reporting Query

Let me pull several of these pieces together into a single query resembling something I’d actually ship for a real dashboard:

SELECT
    category,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE order_date >= CURRENT_DATE - INTERVAL '30 days') AS orders_last_30_days,
    COALESCE(SUM(amount), 0) AS total_revenue,
    ROUND(AVG(amount), 2) AS avg_order_value,
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS most_recent_order_date
FROM orders
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY total_revenue DESC;

This single query answers several distinct business questions at once — volume, recent activity, revenue, average order size, and the lifespan of each category — while excluding categories that don’t have enough orders to be statistically meaningful (HAVING COUNT(*) > 5). This is the kind of query I end up writing constantly once aggregate functions, GROUP BY, HAVING, and FILTER all click together as one cohesive toolkit rather than separate concepts learned in isolation.

Troubleshooting Tips

Error: “column must appear in the GROUP BY clause or be used in an aggregate function.” This is PostgreSQL enforcing that every non-aggregated column in your SELECT list must be part of the grouping. Either add it to GROUP BY or wrap it in an aggregate function.

My HAVING clause isn’t working. Make sure you’re not using WHERE where you meant HAVING. Aggregate conditions always belong in HAVING.

SUM() is returning NULL instead of 0. This happens when there are no matching rows, or all values are NULL. Wrap it in COALESCE(SUM(amount), 0).

My query is slow on a huge table. Aggregate queries over massive tables benefit heavily from indexes on the columns used in WHERE and GROUP BY. Also consider materialized views if the same aggregation runs repeatedly:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

You can refresh it periodically with REFRESH MATERIALIZED VIEW monthly_revenue; instead of recalculating the aggregation on every request.

Best Practices I Follow

  1. Use FILTER instead of nested CASE statements when possible — it’s cleaner and easier to read.
  2. Always account for NULLs in SUM() and AVG() results, especially in financial contexts.
  3. Index the columns used in WHERE and GROUP BY for large tables.
  4. Use COUNT(DISTINCT ...) carefully — it’s more expensive than a plain COUNT(*).
  5. Consider materialized views for aggregations that run frequently but don’t need real-time freshness.
  6. Alias your aggregate columns clearly (AS total_revenue) so the output is self-explanatory.
  7. Test with EXPLAIN ANALYZE on large aggregation queries to catch performance issues early.

Frequently Asked Questions

What’s the difference between COUNT(*) and COUNT(column)? COUNT(*) counts all rows. COUNT(column) counts only rows where that column isn’t NULL.

Can I use multiple aggregate functions in one query? Yes, absolutely. You can combine COUNT, SUM, AVG, MIN, and MAX freely in the same SELECT statement.

Why can’t I filter on an aggregate value using WHERE? Because WHERE is evaluated before grouping and aggregation happen. Use HAVING instead.

Does GROUP BY require sorting the data first? Not necessarily — PostgreSQL’s planner may use a hash aggregate instead of a sort, depending on the data and available memory. You can inspect this with EXPLAIN ANALYZE.

Are aggregate functions case-sensitive with text data? MIN() and MAX() on text follow the collation rules of your database, which are typically case-sensitive by default unless you’re using a case-insensitive collation.

Wrapping Up

Aggregate functions are one of those SQL fundamentals that unlock an entire category of real-world work — reporting, analytics, dashboards, KPIs. Once GROUP BY, HAVING, and FILTER become second nature, you’ll find yourself reaching for them constantly, whether you’re summarizing sales data, counting active users, or building out a monthly report that used to take someone an afternoon in a spreadsheet. Practice these on your own data, check your query plans, and you’ll be writing efficient, readable aggregation queries in no time.

Exit mobile version