SQL GROUP BY

In SQL, we use the GROUP BY clause to group rows based on the value of columns.

Example

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

How to use GROUP BY clause in SQL
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.


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.

How to use GROUP BY clause in SQL
Example: SQL GROUP BY

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.


More on SQL GROUP BY

GROUP BY With Multiple Columns

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

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

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.

Also Read

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges