In SQL, the
GROUP BY clause is used to group rows by one or more columns.
-- 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, ...;
column1, column2 ...are the columns of the table
tableis 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
-- 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
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:
- counts the number of rows by grouping them by country
- returns the result set if their count is greater than 1