MENU


Aggregate

Aggregate functions summarize a group of rows — the whole result set, or each group formed by GROUP BY — into a single value per group.

Core Aggregates

SELECT COUNT(*) FROM tbl;
SELECT id, email, COUNT(*) FROM User GROUP BY id, email HAVING COUNT(*)>1;
SELECT COUNT(DISTINCT program_name) AS Count, program_type
  FROM cm_production
  WHERE push_number=@push_number
  GROUP BY program_type;
SELECT a FROM tbl GROUP BY a HAVING MAX(b)>9;
SELECT id, MAX(score) FROM Test GROUP BY id;
A correlated subquery paired with MAX() is a common way to find the row(s) holding the maximum value per group:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
        FROM shop s2
        WHERE s1.article = s2.article)
ORDER BY article;
SELECT GROUP_CONCAT(DISTINCT score SEPARATOR ' ') FROM class GROUP BY student;
BIT_AND(expr), BIT_OR(expr), and BIT_XOR(expr) perform the corresponding bitwise operation across the values retrieved. STD(expr), STDDEV(expr), or STDDEV_POP(expr) returns the population standard deviation; STDDEV_SAMP(expr) returns the sample standard deviation. VARIANCE(expr) or VAR_POP(expr) returns the population variance; VAR_SAMP(expr) returns the sample variance.

WHERE vs. HAVING

WHERE filters rows based on a condition and is applied before any aggregate function is calculated. HAVING filters the results of a query based on a condition that may involve an aggregate function, and is applied after aggregation. Execution conceptually proceeds:
SELECT customer_id, SUM(total_amount) AS total_orders
FROM orders
WHERE order_date >= '2022-01-01'
GROUP BY customer_id
HAVING SUM(total_amount) > 500;

CREATE TABLE orders (
    customer_id INT,
    order_date  DATE,
    total_amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
    (1, '2022-03-01', 300.00),
    (1, '2022-06-15', 250.00),
    (2, '2022-02-10', 100.00);

-- WHERE filters rows, GROUP BY aggregates, HAVING filters the aggregates
SELECT customer_id, SUM(total_amount) AS total_orders
    FROM orders
    WHERE order_date >= '2022-01-01'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 500;

customer_idtotal_orders
1550.00

Aggregating into JSON

Rows can also be aggregated directly into a JSON array or object; see JSON for the scalar JSON functions.
SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
FROM t3 GROUP BY o_id;

For a t3 table with rows (o_id=2,attribute='color'), (o_id=2,attribute='fabric'), (o_id=3,attribute='color'), (o_id=3,attribute='shape'), this produces o_id=2 → ["color","fabric"] and o_id=3 → ["color","shape"].

SELECT o_id, JSON_OBJECTAGG(attribute, value)
FROM t3 GROUP BY o_id;

For the same rows plus a value column ('red','silk','green', 'square'), this produces o_id=2 → {"color":"red","fabric":"silk"} and o_id=3 → {"color":"green","shape":"square"}.


CREATE TABLE t3 (o_id INT, attribute VARCHAR(20), value VARCHAR(20));
INSERT INTO t3 VALUES
    (2, 'color', 'red'), (2, 'fabric', 'silk'),
    (3, 'color', 'green'), (3, 'shape', 'square');

SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
    FROM t3 GROUP BY o_id;

SELECT o_id, JSON_OBJECTAGG(attribute, value)
    FROM t3 GROUP BY o_id;

SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes FROM t3 GROUP BY o_id:
o_idattributes
2["color", "fabric"]
3["color", "shape"]
SELECT o_id, JSON_OBJECTAGG(attribute, value) FROM t3 GROUP BY o_id:
o_idJSON_OBJECTAGG(attribute, value)
2{"color": "red", "fabric": "silk"}
3{"color": "green", "shape": "square"}