Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 40 ยท SQL Basics

GROUP BY, HAVING and Aggregate Functions in MariaDB

Summarize data in MariaDB with COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, GROUP BY, HAVING and WITH ROLLUP, and learn the difference between WHERE and HAVING and ONLY_FULL_GROUP_BY.

So far each result row corresponded to one stored row. Reports need the opposite: one row that summarizes many, such as "orders per customer" or "average salary per department". Aggregate functions collapse rows into a single value, and GROUP BY decides which rows are collapsed together.

Aggregate functions

Function Result
COUNT(*) number of rows
COUNT(col) number of non-NULL values
COUNT(DISTINCT col) number of different non-NULL values
SUM(col) total
AVG(col) mean (ignores NULL)
MIN(col), MAX(col) smallest and largest
GROUP_CONCAT(col) values joined into one string
STDDEV(col), VARIANCE(col) spread (MariaDB also has MEDIAN() as a window function)

Without GROUP BY the whole table is one group:

SQL Aggregating a whole table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

COUNT(*) counts rows; COUNT(column) skips NULL, so the two can differ. AVG also skips NULL: the average of 10, 20 and NULL is 15, not 10. If missing should count as zero, write AVG(COALESCE(col, 0)).

GROUP BY

GROUP BY splits rows into groups that share the same value(s), and each aggregate is computed per group:

SQL One row per country ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You can group by several columns, and by expressions:

SQL Group by an expression ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The rule behind the errors

Every column in the SELECT list must either appear in GROUP BY or be inside an aggregate. MariaDB enforces that when the ONLY_FULL_GROUP_BY mode is on, as it is in recommended configurations. Without it, a non-grouped column returns an arbitrary row's value, which is a quiet bug. Write queries so that the rule holds, and they will run everywhere.

SQL
-- Error with ONLY_FULL_GROUP_BY: name is not grouped or aggregated
SELECT category_id, name, MAX(price) FROM products GROUP BY category_id;

WHERE versus HAVING

  • WHERE filters rows before grouping.
  • HAVING filters groups after aggregation, so it can use aggregate functions.
SQL WHERE then HAVING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Move a condition into WHERE whenever it does not need an aggregate. The server then discards rows early, and indexes can help. MariaDB lets HAVING use a select alias (HAVING delivered_orders >= 3) as an extension; standard SQL does not.

GROUP_CONCAT

GROUP_CONCAT joins the values of a group into one string, great for lists in a report:

SQL
SELECT category_id,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS products
FROM products
GROUP BY category_id;

The result is cut at group_concat_max_len (1 MB in recent MariaDB, 1024 bytes in older setups), so raise the limit if lists get truncated. SQLite's group_concat(value, separator) is the runnable version:

SQL Lists per group ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Summaries with WITH ROLLUP

MariaDB adds super-aggregate rows (subtotals and a grand total) with WITH ROLLUP:

SQL
SELECT country, city, COUNT(*) AS customers
FROM customers
GROUP BY country, city WITH ROLLUP;

Rows with NULL in city are the subtotal per country, and a row with NULL in both is the grand total. Use GROUPING() to tell a real NULL from a rollup NULL. SQLite has no ROLLUP, but the same result is an easy UNION ALL of two queries:

SQL Subtotal and grand total by hand ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Aggregates over joins

Aggregation usually follows a join. A good habit is to check the row count of the join first, because duplicated rows inflate sums:

SQL Revenue per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

More on this in joins in MariaDB.

Common mistakes

  • Putting an aggregate in WHERE (WHERE COUNT(*) > 1 is an error; use HAVING).
  • Selecting columns that are neither grouped nor aggregated.
  • Forgetting that COUNT(col) skips NULL.
  • Summing after a join that multiplies rows; use COUNT(DISTINCT ...) or aggregate in a subquery first.
  • Integer division: SUM(a) / COUNT(*) in MariaDB returns a decimal (/), while DIV returns an integer.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which clause filters groups after aggregation?

    WHERE filters individual rows before grouping. HAVING filters the groups produced by GROUP BY, so it can use aggregates.
  2. 2. What is AVG(x) for the values 10, 20 and NULL?

    AVG ignores NULL values, so it computes (10+20)/2.
  3. 3. What does GROUP_CONCAT do?

    It builds a delimited list per group. The result is limited by group_concat_max_len.
  4. 4. What does ONLY_FULL_GROUP_BY enforce?

    It stops queries that return arbitrary values for non-grouped columns.
  5. 5. In the output of GROUP BY a, b WITH ROLLUP, what does a row with NULL in b and a value in a mean?

    ROLLUP adds subtotal rows; the grouping column that is rolled up shows NULL.

Summary

  • Aggregates (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT) summarize many rows; COUNT(col) and AVG skip NULL.
  • GROUP BY forms the groups; non-aggregated selected columns must be grouped.
  • WHERE filters rows before grouping, HAVING filters groups after.
  • WITH ROLLUP adds subtotals and a grand total in MariaDB.

Next: UNION, INTERSECT and EXCEPT