GROUP BY

-- 1) Count per group
SELECT  country, COUNT(*) AS users
FROM    users
GROUP   BY country
ORDER   BY users DESC;

-- 2) Multiple aggregates
SELECT  category,
        COUNT(*)         AS n,
        AVG(price)       AS avg_price,
        MIN(price)       AS min_price,
        MAX(price)       AS max_price,
        SUM(stock)       AS total_stock
FROM    products
GROUP   BY category;

-- 3) HAVING — filters GROUPS (not rows)
SELECT  user_id, COUNT(*) AS posts
FROM    posts
GROUP   BY user_id
HAVING  posts >= 10                  -- MySQL allows alias here
ORDER   BY posts DESC;

-- 4) GROUP_CONCAT — comma-separated list per group
SELECT  user_id,
        GROUP_CONCAT(
            DISTINCT tag
            ORDER BY tag
            SEPARATOR ', '
        ) AS tags
FROM    post_tags pt
JOIN    tags t ON t.id = pt.tag_id
GROUP   BY user_id;

-- 5) JSON aggregates — return related rows as a JSON array
SELECT  u.id, u.email,
        JSON_ARRAYAGG(JSON_OBJECT('id', p.id, 'title', p.title)) AS posts
FROM    users u
LEFT    JOIN posts p ON p.user_id = u.id
GROUP   BY u.id, u.email;

-- 6) WITH ROLLUP — subtotal + grand total
SELECT  country, plan, COUNT(*) AS n
FROM    users
GROUP   BY country, plan WITH ROLLUP;
-- Returns per-(country, plan), per-country (plan = NULL), grand (country = NULL, plan = NULL)

-- 7) GROUPING() helps identify the rollup level
SELECT  IFNULL(country, 'All countries') AS country,
        IFNULL(plan,    'All plans')     AS plan,
        COUNT(*) AS n,
        GROUPING(country) AS is_country_total,
        GROUPING(plan)    AS is_plan_total
FROM    users
GROUP   BY country, plan WITH ROLLUP;

-- 8) Strict mode (recommended)
SET sql_mode = CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY');
-- now SELECT-list columns must be in GROUP BY or aggregated — same behaviour as Postgres

SELECT DATE(created_at) AS day, COUNT(*) AS n
FROM events
WHERE created_at >= NOW() - INTERVAL 7 DAY
GROUP BY day
ORDER BY day;