SQL GROUP BY Clause

-- select the item column and the count of order ids from the Orders table
-- group them by the item column

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

SQL GROUP BY Syntax

The syntax of the SQL GROUP BY clause is:

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 from where the rows are selected
• columnA, columnB ... are the column(s) based on which the rows will be grouped

For example,

-- count the number of each country and group the rows by 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 in SQL

Let's try to find the total amount spent by each customer who has placed an order.

-- select customer_id and sum of amount from Orders
-- group the result by customer_id

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.

GROUP BY Clause With JOIN in SQL

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 to read more about the JOIN clause in SQL.

GROUP BY With Multiple Columns

GROUP BY can also be used to group rows based on multiple columns. For example,

-- select country, state, and minimum age from Persons table
-- group by country and state

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

Here, the SQL command groups all persons with similar country and state, and gives the minimum age of each group.

GROUP BY With HAVING Clause

We can use the GROUP BY clause with the HAVING clause to filter the result set based on aggregate functions. For example,

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

Here, the SQL command:

1. counts the number of rows by grouping them by country
2. returns the result set if their count is greater than 1