SQL GROUP BY

-- count the number of orders of each item

SELECT COUNT(order_id), item
FROM Orders
GROUP BY item;

SQL GROUP BY 语法

SELECT column1, column2, ...
FROM table
GROUP BY columnA, columnB, ...;

这里，

• column1, column2 ... 是表的列。
• table 是表的名称。
• columnA, columnB ... 是用于分组行的列。

示例：按每个国家的客户数量分组

-- count the number of customers in each country

SELECT country, COUNT(*) AS number
FROM Customers
GROUP BY country;

这里，SQL 命令按 country 列对行进行分组，并计算每个国家的数量（因为使用了 COUNT() 函数）。

注意：GROUP BY 子句与聚合函数一起使用，例如 MIN() 和 MAX()、SUM() 和 AVG()、COUNT() 等。

由于使用了 AS 别名，编译器会在 number 列中显示 COUNT() 函数的结果。要了解更多信息，请访问 SQL AS 别名。

示例：按每个客户的消费金额分组

-- calculate the total amount spent by each customer

SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id;

这里，SQL 命令在按 customer_id 分组行后，对 amount 进行求和。

带有 JOIN 的 SQL GROUP BY 子句

我们还可以将 GROUP BY 子句与 JOIN 子句一起使用。例如：

-- join the Customers and Orders tables
-- select customer_id and first_name from Customers table
-- also select the count of order ids from Orders table
-- group the result by customer_id

SELECT Customers.customer_id, Customers.first_name,
Count(Orders.order_id) AS order_count
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_id;

这里，SQL 命令连接 Customers 和 Orders 表，并按 customer_id（客户）对结果集进行分组。

这会给出每个客户下的订单数量。

请访问 SQL JOIN 和 SQL LEFT JOIN 以了解有关 SQL 中 JOIN 和 LEFT JOIN 子句的更多信息。

-- group by country and state
--to calculate minimum age of each group

SELECT country, state, MIN(age) AS min_age
FROM Persons
GROUP BY country, state;

-- select the customer_id count and country column from Customers
-- group by country if the count is greater than 1

SELECT COUNT(customer_id), country
FROM Customers
GROUP BY country
HAVING COUNT(customer_id) > 1;

另请阅读

• SQL ORDER BY