Aggregate Functions

Aggregate functions operate on groups of rows and return a single value per group.

COUNT

SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;  -- Excludes NULLs
SELECT COUNT(DISTINCT category) FROM products;

SUM

SELECT SUM(price) FROM products;
SELECT SUM(price) FROM products WHERE category = 'tools';

AVG

SELECT AVG(price) FROM products;
SELECT category, AVG(price) FROM products GROUP BY category;

MIN / MAX

SELECT MIN(price), MAX(price) FROM products;
SELECT category, MIN(price), MAX(price) FROM products GROUP BY category;

GROUP_CONCAT

SELECT category, GROUP_CONCAT(name) FROM products GROUP BY category;
-- tools: "Widget,Gizmo"

SELECT GROUP_CONCAT(name, ' | ') FROM products;
-- "Widget | Gadget | Gizmo"

TOTAL

-- Like SUM but returns 0.0 instead of NULL for empty sets
SELECT TOTAL(price) FROM products WHERE category = 'nonexistent';
-- 0.0 (SUM would return NULL)

Combined Example

SELECT
    category,
    COUNT(*) as count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    SUM(price) as total,
    GROUP_CONCAT(name) as names
FROM products
GROUP BY category
HAVING count > 1
ORDER BY avg_price DESC;