Navigation
KSQL

Grouping & Aggregation

GROUP BY collapses rows that share a value into a single row, typically combined with aggregate functions to summarize each group.

GROUP BY

-- Count by status
SELECT Status, COUNT(*) AS total
FROM Customers
GROUP BY Status

-- Revenue by region and status
SELECT Region, Status, SUM(Revenue) AS revenue, COUNT(*) AS count
FROM Customers
GROUP BY Region, Status

-- Average order by customer
SELECT Customer, AVG(Amount) AS avg_order, COUNT(*) AS orders
FROM Orders
GROUP BY Customer
ORDER BY avg_order DESC

Every column in SELECT that isn't inside an aggregate function must appear in the GROUP BY clause.

HAVING

Filter groups after aggregation. Only valid with GROUP BY.

-- Only statuses with more than 5 customers
SELECT Status, COUNT(*) AS total
FROM Customers
GROUP BY Status
HAVING COUNT(*) > 5

-- Only regions with significant revenue
SELECT Region, SUM(Revenue) AS total_rev
FROM Customers
GROUP BY Region
HAVING SUM(Revenue) > 100000

-- Combined conditions
SELECT Status, COUNT(*) AS cnt, AVG(Revenue) AS avg_rev
FROM Customers
GROUP BY Status
HAVING COUNT(*) > 3 AND AVG(Revenue) > 20000

WHERE filters rows before aggregation; HAVING filters groups after aggregation.

Aggregate functions

Function Description Example
COUNT(*) Count all rows SELECT COUNT(*) FROM Orders
COUNT(column) Count non-null values SELECT COUNT(Email) FROM Customers
COUNT(DISTINCT column) Count unique values SELECT COUNT(DISTINCT Status) FROM Customers
SUM(column) Sum of numeric values SELECT SUM(Revenue) FROM Customers
AVG(column) Average of numeric values SELECT AVG(Amount) FROM Orders
MIN(column) Minimum value SELECT MIN(Price) FROM Products
MAX(column) Maximum value SELECT MAX(Revenue) FROM Customers
GROUP_CONCAT(column) Concatenate values into a string SELECT GROUP_CONCAT(Name) FROM Customers
GROUP_CONCAT(DISTINCT column) Concatenate unique values SELECT GROUP_CONCAT(DISTINCT Status) FROM Customers
GROUP_CONCAT(column, separator) Concatenate with custom separator SELECT GROUP_CONCAT(Name, ' | ')

SUM and AVG automatically cast values to numeric for calculation.

Worked examples

-- Revenue summary by region
SELECT Region,
    COUNT(*) AS customers,
    SUM(Revenue) AS total,
    AVG(Revenue) AS average,
    MIN(Revenue) AS lowest,
    MAX(Revenue) AS highest
FROM Customers
GROUP BY Region
ORDER BY total DESC

-- All tags per category, comma-separated
SELECT Category, GROUP_CONCAT(DISTINCT Tag, ', ') AS all_tags
FROM Products
GROUP BY Category

-- Monthly order volume
SELECT YEAR(OrderDate) AS yr, MONTH(OrderDate) AS mo,
       COUNT(*) AS orders, SUM(Amount) AS revenue
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY yr DESC, mo DESC

Next