What are the common aggregate functions? | Interview Question

Q: What are the common aggregate functions?

**Target Audience:** Beginners writing their first reports, and candidates who want the NULL, empty-set and type rules an interviewer probes. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16; one MySQL fact is cited from its manual. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every result shown was produced by actually running the query; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First At the end of the day a shop owner empties the till and asks five questions. How many sales did we make? How much money is that? What was a typical sale? What was the smallest and the biggest? Nobody answers by reading out every receipt; they squash the pile of receipts into one number per question. That is exactly what an aggregate function does:

The problem

Question presented to candidate: "Using an orders table, walk me through the aggregate functions you use most. What does each return, how do they treat NULLs and empty input, and how would you compute several different totals in one pass over the table?"

What a strong answer should cover:

The core five: COUNT, SUM, AVG, MIN, MAX; each turns many rows into one value (per group with GROUP BY, for the whole table without)
NULL handling: all of them except COUNT(*) skip NULLs, so AVG(col) divides by the non-NULL count; over zero rows COUNT is 0 and the others are NULL
Types: MIN/MAX work on text and dates, SUM/AVG need numbers; integer SUM(x) / COUNT(*) truncates, AVG does not
Conditional aggregation with FILTER (WHERE ...) or SUM(CASE ...), so one scan answers several questions
Beyond the core: string aggregation (string_agg, GROUP_CONCAT), statistics (stddev, percentile_cont), bool_and, and the difference between an aggregate and a window function

Code / implementation expected: Yes — one query with several aggregates, and one with conditional aggregates per region.

Schema used by the examples:

aggregate

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners writing their first reports, and candidates who want the NULL, empty-set and type rules an interviewer probes. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16; one MySQL fact is cited from its manual.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you learn the words an interviewer expects. Every result shown was produced by actually running the query; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

At the end of the day a shop owner empties the till and asks five questions. How many sales did we make? How much money is that? What was a typical sale? What was the smallest and the biggest? Nobody answers by reading out every receipt; they squash

The solution is hidden so you can try first

13 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe core five over the whole table, including MIN and MAX on dates and text

SQLNULLs are skipped, and empty input gives 0 for COUNT but NULL for the rest

SQLConditional aggregation: several totals per region in one pass

PostgreSQL: median, mode, standard deviation, bool_and, string_agg, array_agg

SELECT region,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
       mode() WITHIN GROUP (ORDER BY status) AS usual_status,
       ROUND(stddev_samp(amount), 1) AS stddev_amount,
       bool_and(status = 'shipped') AS all_shipped,
       string_agg(customer, ', ' ORDER BY customer) AS customers,
       array_agg(DISTINCT status ORDER BY status) AS statuses
FROM orders
GROUP BY region
ORDER BY region;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.