I’m writing a query to calculate the total monthly sales per employee and display only the data for April. I’m using PostgreSQL, and while the logic seems fine to me, the output isn’t working as expected — I either get no rows or unexpected behavior.
Here’s the query:
WITH monthly_sales AS (
SELECT
employee_id,
EXTRACT(MONTH FROM sale_date) AS sale_month,
SUM(sale_amount) AS total_sales
FROM sales_data
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY employee_id, sale_month
)
SELECT
e.name,
m.total_sales,
d.department_name
FROM monthly_sales m
JOIN employees e ON e.id = m.employee_id
LEFT JOIN departments d ON d.id = e.department_id
WHERE m.sale_month = 'April';
Answer
WHERE m.sale_month = 'April';
Is the issue here. The sale_month in your CTE (monthly_sales) is derived using EXTRACT(MONTH FROM sale_date), which ... returns a numeric value, not a string like 'April'. (thanks Jonas for the link)
Change it to:
WHERE m.sale_month = 4;