SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example: GROUP BY with COUNT()

SELECT country, COUNT(*) as num_customers
FROM customers
GROUP BY country;

Sample Table

Consider the following customers table:

| id | name         | country    |
|----|--------------|------------|
| 1  | John Doe     | USA        |
| 2  | Jane Smith   | USA        |
| 3  | Mike Johnson | Canada     |
| 4  | Emily Davis  | USA        |
| 5  | David Brown  | Canada     |

Result of GROUP BY

| country    | num_customers |
|------------|---------------|
| USA        | 3             |
| Canada     | 2             |

The number of customers is grouped by country.

Example: GROUP BY with SUM()

SELECT country, SUM(order_amount) as total_sales
FROM orders
GROUP BY country;

Sample Table

Consider the following orders table:

| id | customer_id | country    | order_amount |
|----|--------------|------------|--------------|
| 1  | 1            | USA        | 100          |
| 2  | 2            | USA        | 200          |
| 3  | 3            | Canada     | 150          |
| 4  | 4            | USA        | 300          |
| 5  | 5            | Canada     | 250          |

Result of GROUP BY

| country    | total_sales |
|------------|-------------|
| USA        | 600         |
| Canada     | 400         |

The total sales amount is grouped by country.

Example: GROUP BY with HAVING

SELECT country, COUNT(*) as num_customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

Result of GROUP BY with HAVING

| country    | num_customers |
|------------|---------------|
| USA        | 3             |
| Canada     | 2             |

Countries with more than one customer are returned.