sqlintermediate10 minutes

Fix Bug in SQL Query for Calculating Monthly Sales Totals

You are given a SQL query that is supposed to calculate the total sales amount per month from a sales table. However, the current query returns incorrect results. Identify and fix the bug so the query correctly aggregates sales by month.

Challenge prompt

The following SQL query is intended to calculate the total sales per month by aggregating the amount from each sale grouped by year and month. However, the result does not show correct monthly aggregations. Fix the query so it correctly sums sales grouped by the calendar month and year. Table structure: - sales(sale_id INT, sale_date DATE, amount DECIMAL) Query to fix: SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount) AS total_sales FROM sales GROUP BY month ORDER BY year, month;

Guidance

• Check how the GROUP BY clause is defined. Are you grouping by all the selected non-aggregated columns?
• Remember that when grouping by multiple columns, you have to include all those columns in the GROUP BY clause.

Hints

• The query currently groups only by month, which mixes data from different years.
• Add YEAR(sale_date) to the GROUP BY clause along with MONTH(sale_date).

Starter code

SELECT
  YEAR(sale_date) AS year,
  MONTH(sale_date) AS month,
  SUM(amount) AS total_sales
FROM sales
GROUP BY month
ORDER BY year, month;

Expected output

A result set showing each unique year-month combination with the correct summed total sales amount for that month. For example: year | month | total_sales -----|-------|------------ 2023 | 1 | 10000.00 2023 | 2 | 8500.50 2024 | 1 | 12000.00

Core concepts

GROUP BYaggregate functionsdate functions

Challenge a Friend

Send this duel to someone else and see if they can solve it.