SQL GROUP BY 用法示例

分组就是把一张很长的流水表变成一份报表。先想清楚哪个字段决定一个桶，再对每个桶算计数、总额、平均值或者最大值。一次算好几个汇总也行，数据库照样每个桶只返回一行。

-- how many orders per customer?
SELECT user_id, COUNT(*) AS orders
FROM orders
GROUP BY user_id;

-- revenue per customer, biggest first, top 10
SELECT user_id, SUM(amount) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 10;

-- several aggregates at once (the report query)
SELECT user_id,
       COUNT(*) AS orders, SUM(amount) AS revenue,
       AVG(amount) AS avg_order, MAX(amount) AS biggest
FROM orders GROUP BY user_id;

-- group by a computed value: orders per month (SQLite)
SELECT strftime('%Y-%m', created_at) AS month, COUNT(*)
FROM orders GROUP BY month;

行被收拢之后，你选出来的每个值，在每个桶里都必须只有一个明确的答案。如果五十行源数据能给出五十个不同的名字，数据库不会替你猜。名字属于这个桶的身份，就把它加进分组条件里；只有当你真的确认这些值等价，才用聚合函数把它收起来。

SELECT user_id, name, SUM(amount)   -- ← name is the problem
FROM orders JOIN users ON ...
GROUP BY user_id;

ERROR: column "users.name" must appear in the GROUP BY
clause or be used in an aggregate function

-- concrete fix when one name belongs to each user:
GROUP BY user_id, name

分组之前用行过滤，分组之后用桶过滤。如果你的条件依赖总额或者计数，它就该放在分组这一步之后。查重复靠的也是这个区别：先按邮箱分出一个个桶，再只留下行数超过一的桶。

-- customers who spent 100+ in 2026
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE created_at >= '2026-01-01'    -- rows before grouping
GROUP BY user_id
HAVING SUM(amount) >= 100;          -- buckets after grouping

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- every duplicated email, with its count. works for any column.