SQL GROUP BY

-- count the number of orders of each item

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

SQL GROUP BY Syntax

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

Here,

• column1, column2 ... are the columns of the table
• table is the name of the table
• columnA, columnB ... are the column(s) based on which the rows will be grouped

Example: GROUP BY Number of Customers in Each Country

-- count the number of customers in each country

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

Here, the SQL command groups the rows by the country column and counts the number of each country (because of the COUNT() function).

Note: The GROUP BY clause is used in conjunction with aggregate functions such as MIN() and MAX(), SUM() and AVG(), COUNT(), etc.

Due to the use of the AS alias, the compiler displays the results of the COUNT() function in the number column. To learn more, visit SQL AS Alias.

Example: GROUP BY Amount Spent By Each Customer

-- calculate the total amount spent by each customer

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

Here, the SQL command sums the amount after grouping rows by customer_id.

SQL GROUP BY Clause With JOIN

We can also use the GROUP BY clause with the JOIN clause. For example,

-- 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;

Here, the SQL command joins the Customers and Orders tables and groups the result set by customer_id (a customer).

This gives us the number of orders that are placed by each customer.

Visit SQL JOIN and SQL LEFT JOIN to read more about the JOIN and LEFT JOIN clauses in SQL.

-- 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;

Also Read

• SQL ORDER BY