Filtering by month name after EXTRACT(MONTH) – best practice or mistake?

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 Read more, not a string like 'April'. (thanks Jonas for the link)

Change it to:

WHERE m.sale_month = 4;