Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 40 ยท Functions and Window Functions

Aggregate Functions, GROUP BY and HAVING in MariaDB

Summarize data in MariaDB with COUNT, SUM, AVG, MIN, MAX and GROUP_CONCAT, group rows with GROUP BY and HAVING, add subtotals with WITH ROLLUP and remove duplicates.

An aggregate function collapses many rows into one value: how many orders, the average salary, the latest hire date. Combined with GROUP BY you get one summary row per group, which is the backbone of every report and dashboard.

This lesson covers the standard aggregates, MariaDB's GROUP_CONCAT, filtering groups with HAVING, subtotals with WITH ROLLUP, conditional counting, and how to find and delete duplicate rows safely.

The basic aggregates

Function Result Ignores NULL?
COUNT(*) number of rows counts every row
COUNT(col) rows where col is not NULL yes
COUNT(DISTINCT col) distinct non-NULL values yes
SUM(col), AVG(col) total, mean yes
MIN(col), MAX(col) smallest, largest (works on text and dates) yes
SQL
SELECT COUNT(*)              AS employees,
       COUNT(manager_id)     AS have_manager,
       COUNT(DISTINCT department_id) AS departments,
       ROUND(AVG(salary), 2) AS avg_salary,
       MIN(hire_date)        AS first_hire
FROM employees;

Because aggregates skip NULL, AVG(col) divides by the number of non-NULL values, not by the number of rows. If you want NULL treated as zero, write AVG(COALESCE(col, 0)). On an empty set COUNT returns 0 while SUM, AVG, MIN and MAX return NULL.

SQL Company summary ยท database: company

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

GROUP BY

GROUP BY splits the rows into groups that share the same values in the listed expressions; each aggregate is then computed per group.

SQL
SELECT department_id, COUNT(*) AS headcount, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

MariaDB (like standard SQL) expects every non-aggregated column in the SELECT list to be in the GROUP BY. With the default sql_mode of recent versions the ONLY_FULL_GROUP_BY check is off, so a sloppy query such as SELECT name, department_id, COUNT(*) ... GROUP BY department_id runs and returns an arbitrary name from each group. Treat that as a bug and group by every selected column, or aggregate it (MIN(name)).

You can group by several columns, by expressions, and by select-list aliases or positions (GROUP BY 1, 2), which MariaDB allows.

SQL Headcount and payroll per department ยท database: company

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

HAVING: filtering groups

WHERE filters rows before grouping; HAVING filters groups after the aggregates exist. You cannot write WHERE COUNT(*) > 3.

SQL
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE hire_date >= '2020-01-01'      -- row filter first
GROUP BY department_id
HAVING COUNT(*) >= 2                 -- then group filter
ORDER BY headcount DESC;

Rule of thumb: if a condition does not need an aggregate, put it in WHERE: it is evaluated earlier, shrinks the work, and can use indexes.

SQL Only large departments ยท database: company

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

GROUP_CONCAT: aggregate into a string

GROUP_CONCAT is MariaDB's way to turn a group into a delimited list:

SQL
SELECT department_id,
       GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS people
FROM employees
GROUP BY department_id;

The result is cut at group_concat_max_len (1 MB by default in MariaDB 10.2.4+, only 1024 bytes in older versions), silently producing truncated lists, so check the variable when output looks short. In MariaDB 10.5 and later, JSON_ARRAYAGG and JSON_OBJECTAGG do the same job for JSON.

The portable engine's GROUP_CONCAT(expr, separator) takes the separator as a second argument:

SQL List the people in each department ยท database: company

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

Conditional aggregation

Use SUM over a condition (true is 1, false is 0) or COUNT over a CASE to build several counters in one pass:

SQL
SELECT department_id,
       COUNT(*)                              AS total,
       SUM(salary >= 100000)                 AS high_paid,
       COUNT(CASE WHEN manager_id IS NULL THEN 1 END) AS top_level
FROM employees
GROUP BY department_id;

This "pivot" technique replaces several subqueries and scans the table once.

WITH ROLLUP: subtotals and a grand total

WITH ROLLUP appends summary rows. The extra rows have NULL in the rolled-up columns; use COALESCE to label them (careful: a group whose real value is NULL looks the same).

SQL
SELECT COALESCE(department_id, 'ALL') AS dept,
       COUNT(*) AS headcount
FROM employees
GROUP BY department_id WITH ROLLUP;

The in-browser engine has no ROLLUP, so you can emulate a grand total with UNION ALL:

SQL Totals row with UNION ALL ยท database: company

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

Finding and deleting duplicates

Duplicates usually appear when an import ran twice. First find them:

SQL
SELECT email, COUNT(*) AS copies
FROM subscribers
GROUP BY email
HAVING COUNT(*) > 1;

To keep the lowest id of each e-mail and delete the rest, MariaDB lets you join the table to a derived table in a DELETE:

SQL
DELETE s
FROM subscribers s
JOIN (SELECT email, MIN(id) AS keep_id
      FROM subscribers
      GROUP BY email) k
  ON k.email = s.email AND s.id <> k.keep_id;

Run the matching SELECT first, and wrap the delete in a transaction. Then add a UNIQUE index on email so the problem cannot return. The portable form uses a subquery:

SQL Delete duplicate rows, keep the first

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

MariaDB does not allow DELETE ... WHERE id NOT IN (SELECT ... FROM same_table) to read the table being modified in some forms (error 1093 in MySQL; MariaDB 10.3.1+ permits it). The join form above works everywhere.

Common mistakes

  • Selecting a column that is neither grouped nor aggregated and trusting the value.
  • Using COUNT(col) when you meant COUNT(*) and losing the NULL rows.
  • HAVING used for conditions that belong in WHERE.
  • Averaging averages: the mean of department means is not the company mean. Compute from the base rows.
  • Expecting GROUP BY to sort. Since MariaDB 10.x grouping does not imply ordering; add ORDER BY.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A table has 10 rows and 3 of them have NULL in column c. What does COUNT(c) return?

    COUNT(column) ignores NULL, so only 7 values are counted. COUNT(*) would return 10.
  2. 2. Which clause filters groups after aggregation?

    HAVING sees the aggregate values; WHERE is applied to rows before grouping.
  3. 3. What does GROUP_CONCAT(name ORDER BY name SEPARATOR '; ') return?

    It aggregates the group into one delimited string, limited by group_concat_max_len.
  4. 4. What does WITH ROLLUP add to a GROUP BY result?

    ROLLUP adds higher-level summary rows after the detail rows.

Summary

  • COUNT, SUM, AVG, MIN, MAX ignore NULL; COUNT(*) counts rows.
  • GROUP BY makes one row per group; WHERE filters rows, HAVING filters groups.
  • GROUP_CONCAT builds lists, WITH ROLLUP adds subtotals, conditional aggregates build pivots.
  • Find duplicates with GROUP BY ... HAVING COUNT(*) > 1, delete the extras, then add a unique index.

Next: Window Functions