HexLibraryHexLibrary
</> HexCodeSign inSign up
PythonJavaScriptTypeScriptCC++JavaPHPGoRustRubyBashSQLLuaPerlCOBOLPascalDelphiFortranDartSwiftC#JuliaGroovyRElixirHaskellKotlinScalaClassic ASPClipperMS-DOS
LESSONS
Introduction to SQL (SELECT)Tables (CREATE & INSERT)Filtering (WHERE)Sorting & LimitingAggregate FunctionsUpdate & DeleteJoins (JOIN)
REFERENCE
📖 Overview
🔎 Querying (SELECT)
🗃️ Creating a Table
✏️ Inserting & Modifying Data
🧲 Filtering (WHERE)
🔢 Sorting & Limiting
📊 Aggregate Functions
🔗 Joins (JOIN)
SQL›Aggregate Functions

Aggregate Functions

Summarizing with COUNT, SUM, AVG, MIN, MAX, and GROUP BY.

Simple enough for a 5-year-old to understand

COUNT, SUM, AVG

Example code
1
CREATE TABLE k(price INTEGER);
2
INSERT INTO k VALUES (10), (20), (30);
3
SELECT SUM(price) FROM k;
Total: 60
Output / InputTotal: 60
Output / Input
💡 Hint: COUNT(*) is row count, SUM is total, AVG is average, MIN/MAX is minimum/maximum.

🎯 Exercise & Practice

Test what you've learned — answer, write code, check instantly. (1 left)

1.Add (10), (20), (30) to t(price INTEGER); get the total price (SUM).

🎯 Expected output: 60

Loading editor…

About this topic

SQL functions are divided into two groups, and this distinction is critical. Row functions work separately for each row: converting a text to uppercase, extracting the year from a date. Aggregate functions, on the other hand, reduce a GROUP of rows to a single value: sum, average, count.

Aggregate functions make sense together with GROUP BY: the way to ask the question "how many users are there for each city" is to group the rows by city and count in each group. All reporting is built on this pattern.

To apply a condition to a grouped result, HAVING is used, not WHERE. This is the source of the most common error messages encountered while learning SQL, and the reason is simple: WHERE runs BEFORE grouping, HAVING runs AFTER grouping.

What you will learn

  • COUNT, SUM, AVG, MIN and MAX functions
  • Grouping rows with GROUP BY
  • Filtering grouped results with HAVING
  • Text and date functions
  • Converting NULL values to a default with COALESCE
  • The execution order between WHERE and HAVING

When to use?

Aggregate functions are at the center of every report: monthly total sales, average orders per user, highest-rated product. None of these questions can be answered without GROUP BY.

COALESCE is used to put a meaningful default instead of NULL in reports shown to the outside; a user seeing an empty cell will think it's an error.

Common mistakes

Wrong

SELECT city, COUNT(*) FROM users
WHERE COUNT(*) > 5
GROUP BY city;

WHERE runs BEFORE rows are grouped; at that stage, the COUNT value does not exist yet. The database throws an error like "aggregate functions are not allowed in WHERE". Conditions on grouped results are applied with HAVING.

Correct

SELECT city, COUNT(*) FROM users
GROUP BY city
HAVING COUNT(*) > 5;

Wrong

SELECT city, name, COUNT(*) FROM users
GROUP BY city;

Selecting a column that is not included in the grouping is ambiguous: if there are a hundred users in a city, which name should be displayed? Strict databases consider this an error, some return a random value — the latter is more dangerously silent because it produces incorrect reports.

Correct

SELECT city, COUNT(*) FROM users
GROUP BY city;

Wrong

SELECT SUM(discount) FROM orders;

If there are NULL values in the discount column, SUM skips them; this is usually the desired behavior, but if there are no rows at all, the result returns NULL, not zero. If your reporting code does not expect this NULL, it will throw an error.

Correct

SELECT COALESCE(SUM(discount), 0) FROM orders;

Frequently asked questions

What is the difference between COUNT star and COUNT column?

The star counts all rows. When you give a column name, however, only rows where that column is NOT NULL are counted. This difference leads to unexpected results in columns containing missing data; the answer to the question "how many records are there" is counted with the star.

What is the difference between WHERE and HAVING?

Their execution orders are different. WHERE filters individual rows before grouping; HAVING filters the groups after grouping has taken place. If a condition looks at a single row, it is written in WHERE; if it looks at the total of a group, it is written in HAVING. Using both together in the same query is very common.

Which columns should I write in GROUP BY?

Every column in the SELECT list that is NOT inside an aggregate function. This is the rule and there is no exception; even if some databases are lenient and don't throw an error, the result will be ambiguous. If in doubt, review your SELECT list.

Summary

Aggregate functions produce a single value per group with GROUP BY; all reporting relies on this pattern. The most critical rule is that conditions on grouped results are applied with HAVING, not WHERE.

HexLibraryHexLibrary

Learn programming more clearly. Every command is explained line by line with colors, reinforced with runnable exercises, test your code instantly — all in one place, free.

Learn

  • Home
  • Languages & Lessons
  • HexCode

Company

  • About
  • Contact

Legal

  • Privacy Policy
  • Terms of Use

Other Projects

  • HexToolz
  • Free Service Manuals
© 2026 HexLibrary · The clearest way to learn code