Year vs Month table in postgresql

Question

I'm currently working in a sql project using PostgreSQL, now my results look like this:

month | year | sales
----- + ---- + -----
  7   | 2019 | 5300
  8   | 2019 | 6600
  9   | 2019 | 7100
  7   | 2020 | 6900
  8   | 2020 | 8800
  9   | 2020 | 9500

With this code:

select date_part('month', points_reward.created) as mes,
   date_part('year', points_reward.created) as ano,
   sum(available_points) as "sales"
from points_reward
group by 1,2

I'm trying to create a table that show me the month vs year like this:

      | 2019 | 2020
  --- + ---- + ----
  7   | 5300 | 6900
  8   | 6600 | 8800
  9   | 7100 | 9500

Thanks in advance for any hint or help and for taking the time to read.

I removed the mysql tag. If your question is about PostgreSQL, then you shouldn't tag unrelated database systems like MySQL. — Mark Rotteveel, Sep 10 '20 at 07:07
As a fast shot. Create 2 SELECT statements, one for each year, and use UNION to combine their results. — user743414, Sep 10 '20 at 07:11

score 2 · Accepted Answer · answered Sep 10 '20 at 08:13

By using Filter with SUM function will solve your problem.

select 
date_part('month', points_reward.created) as "Month",
sum(available_points) filter (where date_part('year', points_reward.created)=2019) "2019",
sum(available_points) filter (where date_part('year', points_reward.created)=2020) "2020"
from points_reward
group by 1

DEMO

You can add new columns with further years with same pattern

VSK · Answer 2 · 2020-09-10T07:18:47.037

1

Try something like this:

select cte1.month, cte1.sales as '2019',cte2.sales as '2020' from
(select month, sum(available_points) as sales from points_reward where year=2019 group by month)
 as cte1 inner join
(select month, sum(available_points) as sales from points_reward where year=2020 group by month)
 as cte2 on cte1.month=cte2.month
order by cte1.month

edited Sep 10 '20 at 07:18

answered Sep 10 '20 at 07:13

VSK

108
9

Year vs Month table in postgresql

2 Answers2