SQL functions and aggregate queries
Clear, exam-focused notes on SQL scalar and aggregate functions, GROUP BY, HAVING, NULL handling, and grouping extensions (ROLLUP/GROUPING SETS) with definitions, syntax to remember, worked SQL examples and common mistakes.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
SQL aggregate functions and group queries are used every day by engineers for reporting, analytics, and summarising data (for example, computing sales totals per region or average response time per server). Knowing exactly how aggregates, NULLs, GROUP BY and HAVING work avoids wrong results in dashboards and reports.
Key ideas
GROUP BY: a clause that partitions table rows into groups based on values of one or more columns. Exact rule used in exam answers: when a SELECT contains any non-aggregate column, that column must appear in the GROUP BY list (or be functionally dependent under SQL extensions). If no GROUP BY is present, the entire selected set is treated as one group and aggregate functions compute over all rows. Rows with NULL in a grouping column are grouped together (NULLs are considered equal for grouping).
HAVING: a clause that filters groups after aggregation. Exact statement: HAVING applies to the groups produced by GROUP BY and may use aggregate expressions. Contrast: WHERE filters individual rows before grouping and cannot use aggregates (standard SQL). If used without GROUP BY, HAVING filters the single implicit group formed by the entire result.
NULL handling with aggregates: aggregate functions (SUM, AVG, MIN, MAX, COUNT(expr)) ignore NULL input values. Exception: COUNT(*) counts rows and does not ignore NULLs; it counts every row. If the input to an aggregate is empty: without GROUP BY, the query still returns one row in which SUM/AVG/MIN/MAX are NULL and COUNT() is 0; with GROUP BY and no input rows, there are zero output rows (no groups). In grouping extensions that include the empty grouping set (e.g., ROLLUP/CUBE or GROUPING SETS(())), the grand-total row is returned even if the base table has no rows, with SUM/AVG/MIN/MAX as NULL and COUNT() as 0.
Grouping rules (exam laws):
- In SELECT with GROUP BY: every selected item must be either an aggregate expression or appear in GROUP BY (unless the DBMS supports functional dependency extensions, e.g., MySQL's ONLY_FULL_GROUP_BY off).
- ORDER BY is applied after SELECT and after aggregation; you can ORDER BY grouped columns or aggregate expressions.
- HAVING is evaluated after grouping and after computing aggregate values.
Extensions: ROLLUP, CUBE, GROUPING SETS: SQL provides ways to get subtotals and grand totals in one query. ROLLUP(a, b) produces groups for (a,b), (a), and () (grand total). CUBE(a, b) produces all combinations of a and b (including each alone and grand total). GROUPING SETS explicitly lists group combinations. Exams may ask you to describe rows produced: draw a tree of group combinations or list them.
Diagram to draw (if asked): draw a table with rows labelled by group key values (e.g., dept: A, B, A, C) and an arrow grouping rows by dept into buckets. Label each bucket with computed aggregates (SUM(salary), COUNT(*)). For ROLLUP(a,b), draw boxes for groups (a,b), then a higher-level box grouping by a only, then a final box for grand total; label each box with the columns that remain and note NULL or 'TOTAL' marker for aggregated levels.
Assumptions: standard SQL semantics for NULL and aggregates. Note where DBMS-specific behaviour may vary (see Common mistakes).
Formulas (syntax to remember)
SELECT expr_list FROM table WHERE row_condition GROUP BY col_list HAVING group_condition ORDER BY sort_list; — full order of clauses.
COUNT(*) — counts input rows in the group (including rows with NULL columns). COUNT(1) is equivalent to COUNT(*) in standard SQL (it still counts rows).
COUNT(column) — counts non-NULL values of column in the group. COUNT(DISTINCT column) — counts distinct non-NULL values. COUNT(DISTINCT col1, col2) for multiple columns is supported by some DBMSs; the SQL standard added it in SQL:2003 and not all systems support it.
SUM(column) — sum of non-NULL numeric values in the group; returns NULL if no non-NULL values.
AVG(column) — average of non-NULL numeric values in the group; returns NULL if no non-NULL values. SUM(DISTINCT column) and AVG(DISTINCT column) operate on the set of distinct non-NULL values.
MIN(column) — minimum of non-NULL values in the group; returns NULL if all values NULL.
MAX(column) — maximum of non-NULL values in the group; returns NULL if all values NULL.
GROUP BY col1, col2 — groups rows by unique combinations of col1 and col2.
HAVING SUM(sales) > 1000 — example of filtering groups using aggregate.
GROUP BY ROLLUP(col1, col2) — groups for (col1,col2), (col1), and grand total.
GROUP BY GROUPING SETS ((col1), (col2, col3), ()) — explicit set of groupings.
Worked examples
All examples use standard SQL (works on PostgreSQL, Oracle with small syntax differences; some DBs may return numeric types differently). Table creation and inserts are shown; outputs are plain text tables.
Example 1 — basic aggregate and NULL handling SQL:
CREATE TABLE employees(emp_id INT, dept VARCHAR(10), salary INT);
INSERT INTO employees VALUES
(1,'HR',5000), (2,'HR',NULL), (3,'ENG',7000), (4,'ENG',6000), (5,'MKT',NULL);
SELECT dept,
COUNT(*) AS cnt_rows,
COUNT(salary) AS cnt_salary_not_null,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY dept
ORDER BY dept;
Output (exact text table):
dept | cnt_rows | cnt_salary_not_null | total_salary | avg_salary | min_salary | max_salary
-----+----------+---------------------+--------------+------------+------------+-----------
ENG | 2 | 2 | 13000 | 6500 | 6000 | 7000
HR | 2 | 1 | 5000 | 5000 | 5000 | 5000
MKT | 1 | 0 | NULL | NULL | NULL | NULL
Explanation (steps): groups formed by dept. Notice COUNT(*) counts rows (MKT has 1), COUNT(salary) ignores NULL (MKT -> 0). SUM/AVG/MIN/MAX return NULL for MKT since no non-NULL salary values.
Example 2 — WHERE vs HAVING SQL:
CREATE TABLE sales(sale_id INT, product VARCHAR(10), qty INT, price INT);
INSERT INTO sales VALUES
(1,'P1',10,100), (2,'P1',5,100), (3,'P2',2,200), (4,'P2',NULL,200), (5,'P3',1,50);
-- Want total quantity per product but only for rows with qty IS NOT NULL
SELECT product, SUM(qty) AS total_qty
FROM sales
WHERE qty IS NOT NULL
GROUP BY product
HAVING SUM(qty) > 5
ORDER BY product;
Output:
product | total_qty
--------+----------
P1 | 15
Explanation: WHERE removed the row with NULL qty before grouping. If instead we needed to exclude groups with small totals we use HAVING. For example, omitting WHERE and using HAVING SUM(qty) > 5 would treat NULL as ignored in SUM and could produce same or different result depending on data.
Example 3 — ROLLUP for subtotals SQL:
CREATE TABLE orders(region VARCHAR(10), category VARCHAR(10), amount INT);
INSERT INTO orders VALUES
('North','A',100),('North','B',150),('South','A',200),('South','B',100);
SELECT region, category, SUM(amount) AS total_amt
FROM orders
GROUP BY ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;
Output (table rows listed explicitly):
region | category | total_amt
-------+----------+----------
North | A | 100
North | B | 150
North | NULL | 250 <-- subtotal for North
South | A | 200
South | B | 100
South | NULL | 300 <-- subtotal for South
NULL | NULL | 550 <-- grand total
Note: Many exam answers expect you to explain the NULL in grouping result is the placeholder for the grouped-away column; some DBMSes provide GROUPING() or GROUPING_ID() to mark subtotal rows explicitly.
Common mistakes
- Expecting SUM/AVG to treat NULL as zero — they ignore NULLs; check COUNT behavior: COUNT(col) ignores NULL, COUNT(*) counts rows.
- Not ordering results after aggregation when order matters for answer — ORDER BY happens after aggregation.
- Integer division confusion: some DBMSs return integer type for AVG on integer columns; others return a fractional numeric. If exact decimals matter in an exam, cast to DECIMAL/NUMERIC or use AVG(CAST(col AS NUMERIC)).
- Misreading ROLLUP/CUBE output: forget that ROLLUP produces hierarchical subtotals and one grand total, while CUBE produces all combinations.
For your exam
Question types you will see: definitions (define aggregate/scalar/GROUP BY/HAVING), short notes (NULL handling, COUNT differences), derivations or laws (show why non-aggregated columns must be in GROUP BY), SQL writing (write query to compute totals, subtotals, or top-k groups), and program-style questions showing table contents and asking for query results.
What a full-marks answer includes:
- For definitions: precise statement and example.
- For queries: complete SQL that runs, explanation of each clause (why WHERE/HAVING used), and the exact output table (rows and order).
- For theory: clear mention of NULL semantics, COUNT(*) vs COUNT(col), GROUP BY rule, and any DBMS-specific caveats if relevant.
Quick check
- What is the difference between WHERE and HAVING?
- What does COUNT(col) return if all rows have NULL in that column for a group?
- Which clause must list every non-aggregated column used in SELECT? Answers: WHERE filters rows before aggregation; HAVING filters groups after aggregation. COUNT(col) returns 0 when all values are NULL in the group (no non-NULL values counted). GROUP BY must list every non-aggregated column used in SELECT.
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?