Grouping

Grouping

The GROUP BY clause comes after the FROM clause of the SELECT statement. In case a statement contains a WHERE clause, the GROUP BY clause must come after the WHERE clause.

SELECT

column_1,

aggregate_function(column_2)

FROM table

(WHERE ......)

GROUP BY

column_1,

column_2;

HAVING conditions

Following the GROUP BY clause is a column or a list of comma-separated columns used to specify the group. ³

如果两个primary key values指向同一非primary key值，grouping之后重复的非primary key值只会出现一次：

GROUP BY OFFICE之后：

---

Example:

Which ORDERS are invoiced for a quantity of 20000 or more items?

dbGetQuery(mydb, 'SELECT ORDER_NO, SUM(QUANTITY) FROM INVOICES GROUP BY ORDER_NO HAVING SUM(QUANTITY) > 20000')

# ORDER_NO SUM(QUANTITY)

# 1 1 20120

How many items are in each order?

dbGetQuery(mydb, 'SELECT ORDER_NO, SUM(QUANTITY) FROM INVOICES GROUP BY ORDER_NO')

# ORDER_NO SUM(QUANTITY)

# 1 1 20120

# 2 2 11000

---

常与ordering一起使用

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

ORDER BY column_name(s);

Example:

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders

LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID

GROUP BY ShipperName;