15 SQL GROUP BY Practice Problems with Solutions

Solution

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Explanation

GROUP BY customer_id groups all rows with the same customer_id together. COUNT(*) then counts how many rows are in each group, giving the total number of orders per customer. AS order_count gives the result column a readable name.

Solution

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Explanation

GROUP BY department_id creates one group per department. COUNT(*) counts how many employee rows fall into each group. The result shows one row per department with the total number of employees in it.

Solution

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

Explanation

GROUP BY category groups all products that share the same category. COUNT(*) counts how many products are in each category group, returning one summary row per category.

Solution

SELECT issue_type, COUNT(*) AS ticket_count
FROM support_tickets
GROUP BY issue_type;

Explanation

GROUP BY issue_type groups tickets by their issue category. COUNT(*) counts the total number of tickets in each group, making it easy to see which types of issues are most common.

Solution

SELECT driver_id, COUNT(*) AS ride_count
FROM rides
WHERE status = 'Completed'
GROUP BY driver_id;

Explanation

WHERE status = 'Completed' filters to only completed rides before grouping. GROUP BY driver_id then groups the filtered rows by driver, and COUNT(*) counts how many completed rides each driver has. WHERE always runs before GROUP BY.

Solution

SELECT category, SUM(revenue_usd) AS total_revenue
FROM sales
GROUP BY category;

Explanation

GROUP BY category groups all sales by their category. SUM(revenue_usd) then adds up the revenue for every sale within each group, giving the total revenue per category.

Solution

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Explanation

GROUP BY department_id groups employees by their department. AVG(salary) calculates the average salary across all employees in each group, returning one row per department.

Solution

SELECT customer_id, SUM(total_usd) AS total_spend
FROM orders
GROUP BY customer_id;

Explanation

GROUP BY customer_id groups all orders belonging to the same customer. SUM(total_usd) adds up all the order totals for each customer, giving their lifetime spend.

Solution

SELECT distance_category, AVG(fare_usd) AS avg_fare
FROM rides
GROUP BY distance_category;

Explanation

GROUP BY distance_category groups rides into their distance bands. AVG(fare_usd) then calculates the average fare paid within each distance category, making it easy to compare pricing across trip lengths.

Solution

SELECT product_name, SUM(quantity_sold) AS total_quantity, AVG(revenue_usd) AS avg_price
FROM sales
GROUP BY product_name;

Explanation

GROUP BY product_name groups all sales for each product. SUM(quantity_sold) totals the units sold and AVG(revenue_usd) calculates the average sale value — both applied to the same groups in a single query.

Solution

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

Explanation

GROUP BY customer_id groups orders by customer and COUNT(*) counts orders per customer. HAVING COUNT(*) > 3 then filters to only keep groups where the count exceeds 3. HAVING is used here instead of WHERE because we're filtering on an aggregated value.

Solution

SELECT category, SUM(revenue_usd) AS total_revenue
FROM sales
GROUP BY category
HAVING SUM(revenue_usd) > 10000;

Explanation

SUM(revenue_usd) totals revenue per category. HAVING SUM(revenue_usd) > 10000 filters out any category whose total revenue is $10,000 or less, leaving only the high-performing categories.

Solution

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000
ORDER BY avg_salary DESC;

Explanation

GROUP BY department_id groups employees by department. HAVING AVG(salary) > 70000 keeps only departments whose average salary exceeds $70,000. ORDER BY avg_salary DESC then sorts those departments from the highest average salary down.

Solution

SELECT issue_type, COUNT(*) AS open_ticket_count
FROM support_tickets
WHERE status = 'Open'
GROUP BY issue_type
HAVING COUNT(*) > 10
ORDER BY open_ticket_count DESC;

Explanation

WHERE status = 'Open' filters to only open tickets before grouping. GROUP BY issue_type groups the filtered rows by issue type. HAVING COUNT(*) > 10 keeps only issue types with more than 10 open tickets. ORDER BY open_ticket_count DESC sorts the results from the most common issue type down. Notice WHERE filters rows before grouping while HAVING filters groups after aggregation.

Solution

SELECT driver_id, SUM(fare_usd) AS total_earned
FROM rides
WHERE status = 'Completed'
GROUP BY driver_id
HAVING SUM(fare_usd) > 500
ORDER BY total_earned DESC;

Explanation

WHERE status = 'Completed' ensures only completed rides count toward earnings. GROUP BY driver_id groups rides by driver. SUM(fare_usd) totals their earnings. HAVING SUM(fare_usd) > 500 keeps only drivers who earned more than $500. ORDER BY total_earned DESC ranks them from highest earner down.