SQL GROUP BY

In SQL, the GROUP BY clause is used to group rows by one or more columns. For example,

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(), MAX(), SUM(), AVG() and COUNT(), etc.

Here, the column name of the COUNT() function in the result-set is number, because of the AS alias. To learn more, visit SQL AS Alias.

Example Two: GROUP BY in SQL

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

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

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

GROUP BY Clause With JOIN in SQL

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

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

To learn more, visit SQL HAVING Clause.