GROUP BY

GROUP BY

GROUP BY groups rows that have the same value in one or more columns, so you can run aggregate functions per group.

Count customers per country

SELECT Country, COUNT(CustomerID)
FROM Customers
GROUP BY Country;

Result:

The rule

Every column in SELECT that is not an aggregate must appear in GROUP BY.

Group by multiple columns

SELECT Country, City, COUNT(CustomerID)
FROM Customers
GROUP BY Country, City;

Group with other aggregates

SELECT CategoryID, AVG(Price), MAX(Price)
FROM Products
GROUP BY CategoryID;

ORDER of execution

SELECT Country, COUNT(*)
FROM Customers
GROUP BY Country
ORDER BY COUNT(*) DESC;

• SELECT ... FROM ...
• WHERE ... (filter rows)
• GROUP BY ... (group rows)
• ORDER BY ... (sort the groups)

TL;DR

• GROUP BY groups rows sharing the same value.
• Aggregates then run per group.
• Non-aggregate columns in SELECT must be in GROUP BY.
• It goes after WHERE, before ORDER BY.