MySQL Aggregate & Grouping Functions — Complete Guide 2026

Introduction#

Why This Matters#

Aggregate functions are the backbone of data analysis in SQL. Every time you need to answer a business question — "What is our total revenue by city?" or "Which product category has the most items?" — you reach for aggregate functions. Without them, you would need to pull every row into application code and compute totals manually, which is slow and error-prone.

Understanding GROUP BY and HAVING alongside aggregate functions lets you write concise, performant queries that databases execute in optimised execution plans. This knowledge is tested in virtually every SQL interview and used daily by analysts, back-end engineers, and data scientists.

The Sample Table: shoppe#

All examples in this guide use the following table:

CREATE TABLE shoppe (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(30),
  price        DECIMAL(8,2),
  qty          INT,
  city         VARCHAR(30),
  company      VARCHAR(20)
);

Core Aggregate Functions#

COUNT — Counting Rows and Values#

COUNT(*) counts every row regardless of NULL values. COUNT(fieldname) counts only non-NULL values in that column. COUNT(DISTINCT fieldname) counts unique non-NULL values.

-- Total number of rows
SELECT COUNT(*) FROM shoppe;
-- Output: 7
 
-- Non-NULL prices
SELECT COUNT(price) FROM shoppe;
-- Output: 6  (assumes one row has NULL price)
 
-- Distinct cities
SELECT COUNT(DISTINCT city) FROM shoppe;
-- Output: 3

SUM and AVG — Totals and Averages#

SUM and AVG work only on numeric columns (INT, DECIMAL). AVG excludes NULL values from both the numerator and denominator.

-- Total price across all products
SELECT SUM(price) FROM shoppe;
-- Output: 2300.00
 
-- Average quantity (NULLs excluded automatically)
SELECT AVG(qty) FROM shoppe;
-- Output: 28.00

MIN and MAX — Extremes#

MIN and MAX work on numeric, date, and string columns. On strings they use alphabetical ordering.

SELECT MAX(price) FROM shoppe;
-- Output: 600.00
 
SELECT MIN(qty) FROM shoppe;
-- Output: 5
 
-- Works on strings too
SELECT MIN(product_name) FROM shoppe;
-- Output: Camera  (alphabetically first)

GROUP BY — Grouping Records#

GROUP BY partitions all rows into groups based on one or more column values, then applies the aggregate function to each group independently.

Rule: Every column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause.

-- Total sales revenue per city
SELECT city, SUM(price * qty) AS total_sales
FROM shoppe
GROUP BY city;

-- Average product price per company
SELECT company, AVG(price) AS avg_price
FROM shoppe
GROUP BY company;

-- Count of products per city
SELECT city, COUNT(*) AS city_count
FROM shoppe
GROUP BY city;

HAVING — Filtering Groups#

HAVING filters the result of GROUP BY the same way WHERE filters individual rows. The critical distinction: WHERE runs before grouping; HAVING runs after.

-- Cities with total sales above 10000
SELECT city, SUM(price * qty) AS total_sales
FROM shoppe
GROUP BY city
HAVING SUM(price * qty) > 10000;

-- Companies that supply more than one product
SELECT company, COUNT(*) AS product_count
FROM shoppe
GROUP BY company
HAVING COUNT(*) > 1;

WHERE vs HAVING — Key Difference#

-- WHERE filters rows BEFORE grouping
SELECT city, SUM(price * qty) AS total_sales
FROM shoppe
WHERE price > 200          -- only rows with price > 200 are grouped
GROUP BY city
HAVING SUM(price * qty) > 5000;  -- then filter the groups

Using HAVING without GROUP BY applies the filter to the entire table as a single group, which is rarely useful.

Common Mistakes#

• Selecting non-aggregated columns without GROUP BY. MySQL in strict mode raises an error; in lenient mode it returns an arbitrary value.
• Using WHERE on aggregate results. You cannot write WHERE SUM(qty) > 50 — use HAVING instead.
• Confusing COUNT(*) and COUNT(column). COUNT(*) never ignores rows; COUNT(column) skips NULLs.
• Forgetting that AVG ignores NULLs. A column with 5 values and 2 NULLs averages over 5, not 7.
• Expecting ORDER in GROUP BY output. Results are unordered unless you add ORDER BY.

Best Practices#

• Always add an ORDER BY clause after GROUP BY for deterministic output.
• Use column aliases in HAVING only when the database supports it; prefer repeating the expression for portability.
• Index columns that appear in GROUP BY to allow the query optimiser to use a sort-free grouping strategy.
• Combine WHERE and HAVING: filter rows cheaply with WHERE first, then filter groups with HAVING.
• Prefer COUNT(*) over COUNT(1) — both are equivalent, but COUNT(*) is the SQL standard.

Key Takeaways#

• COUNT(*) counts all rows including those with NULLs; COUNT(column) skips NULLs.
• SUM() and AVG() operate only on numeric data types; MIN() and MAX() work on any comparable type.
• GROUP BY divides rows into partitions before applying aggregate functions to each partition.
• HAVING filters groups produced by GROUP BY, while WHERE filters individual rows before grouping.
• Every non-aggregate column in SELECT must appear in GROUP BY (SQL standard rule).
• Combining WHERE and HAVING in one query allows both row-level and group-level filtering in a single pass.
• COUNT(DISTINCT column) counts unique non-NULL values and is useful for finding cardinality.
• Aggregate queries without GROUP BY treat the entire table as one group and return a single result row.