SQL Server: SQL GROUP BY Exercises (With Answers)

This guide is specifically for SQL Server syntax.

Learn SQL GROUP BY with hands-on practice exercises. Each exercise includes the table schema, solution query, expected output, and detailed explanation.

What You'll Practice

These exercises cover GROUP BY from basic to advanced concepts. Work through each problem to build your SQL skills.

Exercise 1

Question: Count the number of employees in each department.

Table Schema

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

Solution

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP 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

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

Table Schema

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

Solution

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER 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

Question: Find total revenue by product category and month.

Table Schema

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

Solution

SELECT 
  product_category,
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS total_revenue
FROM orders
GROUP BY product_category, DATE_TRUNC('month', order_date)
ORDER 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.

Common Mistakes to Avoid

• Forgetting to use proper syntax for GROUP BY
• Not considering NULL values in comparisons
• Confusing similar operators or clauses

Related SQL Exercises

Continue practicing with these related topics:

SQL Server-Specific Notes

This page covers SQL Server syntax. Other databases may have different syntax for similar operations.