SQL Exercises

SQL GROUP BY Exercises (With Answers)

12 Exercises
~60 min
8 Intermediate4 Advanced

Exercise 1

intermediate

Question

Count the number of employees in each department.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, COUNT(*) AS employee_count
2FROM employees
3GROUP BY department;

Expected Output

| department | employee_count |
|---|---|
| Sales | 5 |
| Engineering | 8 |

Explanation

GROUP BY groups rows by one or more columns. Aggregate functions like COUNT operate on each group.

Exercise 1

intermediate

Question

Count the number of employees in each department.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, COUNT(*) AS employee_count
2FROM employees
3GROUP BY department;

Expected Output

| department | employee_count |
|---|---|
| Sales | 5 |
| Engineering | 8 |

Explanation

GROUP BY groups rows by one or more columns. Aggregate functions like COUNT operate on each group.

Exercise 1

intermediate

Question

Count the number of employees in each department.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, COUNT(*) AS employee_count
2FROM employees
3GROUP BY department;

Expected Output

| department | employee_count |
|---|---|
| Sales | 5 |
| Engineering | 8 |

Explanation

GROUP BY groups rows by one or more columns. Aggregate functions like COUNT operate on each group.

Exercise 1

intermediate

Question

Count the number of employees in each department.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, COUNT(*) AS employee_count
2FROM employees
3GROUP BY department;

Expected Output

| department | employee_count |
|---|---|
| Sales | 5 |
| Engineering | 8 |

Explanation

GROUP BY groups rows by one or more columns. Aggregate functions like COUNT operate on each group.

Exercise 2

intermediate

Question

Calculate the average salary by department, ordered highest to lowest.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, AVG(salary) AS avg_salary
2FROM employees
3GROUP BY department
4ORDER BY avg_salary DESC;

Expected Output

| department | avg_salary |
|---|---|
| Engineering | 75000 |
| Sales | 55000 |

Explanation

You can use column aliases in ORDER BY. The query calculates average salary per department.

Exercise 2

intermediate

Question

Calculate the average salary by department, ordered highest to lowest.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, AVG(salary) AS avg_salary
2FROM employees
3GROUP BY department
4ORDER BY avg_salary DESC;

Expected Output

| department | avg_salary |
|---|---|
| Engineering | 75000 |
| Sales | 55000 |

Explanation

You can use column aliases in ORDER BY. The query calculates average salary per department.

Exercise 2

intermediate

Question

Calculate the average salary by department, ordered highest to lowest.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, AVG(salary) AS avg_salary
2FROM employees
3GROUP BY department
4ORDER BY avg_salary DESC;

Expected Output

| department | avg_salary |
|---|---|
| Engineering | 75000 |
| Sales | 55000 |

Explanation

You can use column aliases in ORDER BY. The query calculates average salary per department.

Exercise 2

intermediate

Question

Calculate the average salary by department, ordered highest to lowest.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);
Show Solution

Solution

SQL
1SELECT department, AVG(salary) AS avg_salary
2FROM employees
3GROUP BY department
4ORDER BY avg_salary DESC;

Expected Output

| department | avg_salary |
|---|---|
| Engineering | 75000 |
| Sales | 55000 |

Explanation

You can use column aliases in ORDER BY. The query calculates average salary per department.

Exercise 3

advanced

Question

Find total revenue by product category and month.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  product_category VARCHAR(50),
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT 
2  product_category,
3  DATE_TRUNC('month', order_date) AS month,
4  SUM(amount) AS total_revenue
5FROM orders
6GROUP BY product_category, DATE_TRUNC('month', order_date)
7ORDER BY month, total_revenue DESC;

Expected Output

| product_category | month | total_revenue |
|---|---|---|
| Electronics | 2024-01-01 | 150000 |

Explanation

Group by multiple columns for multi-dimensional analysis. DATE_TRUNC normalizes dates to month boundaries.

Exercise 3

advanced

Question

Find total revenue by product category and month.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  product_category VARCHAR(50),
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT 
2  product_category,
3  DATE_TRUNC('month', order_date) AS month,
4  SUM(amount) AS total_revenue
5FROM orders
6GROUP BY product_category, DATE_TRUNC('month', order_date)
7ORDER BY month, total_revenue DESC;

Expected Output

| product_category | month | total_revenue |
|---|---|---|
| Electronics | 2024-01-01 | 150000 |

Explanation

Group by multiple columns for multi-dimensional analysis. DATE_TRUNC normalizes dates to month boundaries.

Exercise 3

advanced

Question

Find total revenue by product category and month.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  product_category VARCHAR(50),
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT 
2  product_category,
3  DATE_TRUNC('month', order_date) AS month,
4  SUM(amount) AS total_revenue
5FROM orders
6GROUP BY product_category, DATE_TRUNC('month', order_date)
7ORDER BY month, total_revenue DESC;

Expected Output

| product_category | month | total_revenue |
|---|---|---|
| Electronics | 2024-01-01 | 150000 |

Explanation

Group by multiple columns for multi-dimensional analysis. DATE_TRUNC normalizes dates to month boundaries.

Exercise 3

advanced

Question

Find total revenue by product category and month.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  product_category VARCHAR(50),
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT 
2  product_category,
3  DATE_TRUNC('month', order_date) AS month,
4  SUM(amount) AS total_revenue
5FROM orders
6GROUP BY product_category, DATE_TRUNC('month', order_date)
7ORDER BY month, total_revenue DESC;

Expected Output

| product_category | month | total_revenue |
|---|---|---|
| Electronics | 2024-01-01 | 150000 |

Explanation

Group by multiple columns for multi-dimensional analysis. DATE_TRUNC normalizes dates to month boundaries.

Related Content

Continue Learning

exercisePostgreSQL: SQL GROUP BY Exercises (With Answers)
exerciseSQL Server: SQL GROUP BY Exercises (With Answers)
exerciseMySQL: SQL GROUP BY Exercises (With Answers)
exerciseSQL SELECT Statement Exercises (With Answers)
exerciseSQL WHERE Clause Exercises (With Answers)

From Our Blog

The Power of Grouping: GROUP BYGROUP BY Performance SecretsThe Five Most Common GROUP BY Mistakes

Ready for more practice?

Join SQL Mastery and get access to interactive exercises, quizzes, and more.