How to output results for each year in a new column?

Question

This code shows data in a one line, but I need results of each year in separat column. Could you help me?

SELECT 
EXTRACT (YEAR FROM s.time_id)::int,
p.prod_subcategory,
sum(s.amount_sold)
FROM sales s
JOIN products p ON p.prod_id = s.prod_id
WHERE EXTRACT (YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY EXTRACT (YEAR FROM s.time_id)::int, p.prod_subcategory
ORDER BY date_part;

score 1 · Accepted Answer · answered Oct 27 '20 at 18:49

Use conditional aggregation:

SELECT p.prod_subcategory,
       SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 2000) as sales_2000,
       SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 1999) as sales_1999,
       SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 1998) as sales_1998
FROM sales s JOIN
     products p
     ON p.prod_id = s.prod_id
WHERE EXTRACT (YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY p.prod_subcategory;

score 0 · Answer 2 · answered Oct 27 '20 at 19:05

Simple but inflexible hard-coded way - use filter clause:

SELECT
  p.prod_subcategory,
  sum(s.amount_sold) filter (where EXTRACT(YEAR FROM s.time_id)::int = 1998) as amount_1998,
  sum(s.amount_sold) filter (where EXTRACT(YEAR FROM s.time_id)::int = 1999) as amount_1999,
  sum(s.amount_sold) filter (where EXTRACT(YEAR FROM s.time_id)::int = 2001) as amount_2001
FROM sales s
JOIN products p ON p.prod_id = s.prod_id
WHERE EXTRACT(YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY p.prod_subcategory
ORDER BY _whatever-you-need_;

A more generic and elegant way would be to use extension tablefunc, function crosstab. Here is a SO thread for that.

How to output results for each year in a new column?

2 Answers2