Learn about SQL GROUP BY Clause with the help of examples. Learn how to use the SQL GROUP BY clause to group rows in your queries based on common values in one or more columns.
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;
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 tabletable
is the name of the table from where the rows are selectedcolumnA, columnB ...
are the column(s) based on which the rows will be groupedFor 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.
Example: SQL GROUP BY
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.
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.
Example: SQL GROUP BY
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.
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.
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:
#sql