sql sales data grouped by year in separate columns in postgresql

I have two input tables:

• analysis (an_id, an_name, an_cost, an_price, an_group)
• orders (ord_id, ord_datetime, ord_an) # orders of analysis (sales)

For every analysis_id I need to show the amount of orders for years 2020 and 2019.

Expected output:

an	year2019	year2020
1	32	41
2	29	28
3	42	37
4	26	35
5	32	34

logic in my query:

• step1 – get orders table data only for years 2019,2020 – use CTE and extract()
• step2 – aggregate by year

My query:

WITH helper AS (
    SELECT an_id, 
           ord_id, 
           EXTRACT(year from ord_datetime) as year
    FROM analysis a 
    INNER JOIN orders o ON o.ord_an = a.an_id
    WHERE EXTRACT(year FROM ord_datetime) in (2019.0,2020.0)
)
SELECT an_id, 
       CASE WHEN year = 2019.0 THEN COUNT(ord_id) ELSE 0 END AS year2019,
       CASE WHEN year = 2020.0 THEN COUNT(ord_id) ELSE 0 END AS year2020
FROM helper  
GROUP BY year, an_id
ORDER BY an_id

The current output of my query:

an_id	year2019	year2020
1	32	0
1	0	41
2	29	0
2	0	28
3	42	0

Answer

The issue in your query may be inside your GROUP BY clause, because you’re grouping on the year too. Instead consider the following approach, where you invert the position of the COUNT aggregate function and the CASE statement:

SELECT a.an_id,
       COUNT(CASE WHEN EXTRACT(year FROM o.ord_datetime) = 2019 THEN o.ord_id END) AS year2019,
       COUNT(CASE WHEN EXTRACT(year FROM o.ord_datetime) = 2020 THEN o.ord_id END) AS year2020
FROM       analysis a
INNER JOIN orders o
        ON o.ord_an = a.an_id
GROUP BY a.an_id

Note: the ELSE part of your CASE statement is not necessary, as long as values will be defaulted to NULL (and not counted in by the COUNT).