How to create a pivot table in Postgresql using case when?

Question

I want to create a pivot table using postgresql. I could accomplish this using SQLite, and I thought the logic would be similar, but it doesn't seem to be the case.

Here's the sample table:

create table df(
  campaign varchar(50),
date date not null,
revenue integer not null
);

insert into df(campaign,date,revenue) values('A','2019-01-01',10000);
insert into df(campaign,date,revenue) values('B','2019-01-02',7000);
insert into df(campaign,date,revenue) values('A','2018-01-01',5000);
insert into df(campaign,date,revenue) values('B','2018-01-01',3500);

here's my sqlite code to transform the tidy data into pivot table:

    select 
    sum(case when strftime('%Y', date) = '2019' then revenue else 0 end) as '2019',
    sum(case when strftime('%Y', date) = '2018' then revenue else 0 end) as '2018',
campaign
    from df
group by campaign

the result would be like this:

2018    2019    campaign
5000    10000   A
3500    7000    B

I tried making the similar code using postgres, I will just use the year 2019:

select 

sum(case when extract('year' from date) = '2019' then revenue else 0 end) as '2019',
campaign
from df
group by campaign

somehow the code doesn't work, I don't understand what's wrong.

Query Error: error: syntax error at or near "'2019'"

what do I miss here?

db-fiddle link: https://www.db-fiddle.com/f/f1WjMAAxwSPRvB8BrxECN7/0

forpas · Accepted Answer · 2021-04-04 12:05:28Z

1

The function strftime() is used to extract various parts of a date in SQLite, but is not supported by Postgresql.
Use date_part():

select campaign,
  sum(case when date_part('year', date) = '2019' then revenue else 0 end) as "2019",
  sum(case when date_part('year', date) = '2018' then revenue else 0 end) as "2018"
from df
group by campaign

Or use Postgresql's FILTER clause:

select campaign,
  sum(revenue) filter (where date_part('year', date) = '2019') as "2019",
  sum(revenue) filter (where date_part('year', date) = '2018') as "2018"
from df
group by campaign

Also, don't use single quotes for table/column names.
SQLite allows it but Postgresql does not.
It accepts only double quotes which is the SQL standard.

See the demo.

edited Apr 4, 2021 at 12:05

answered Apr 4, 2021 at 11:59

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

calvindewilde Over a year ago

this works perfectly, thank you! and thanks for the tips about single/double quote. they seem the source of the problem.

Collectives™ on Stack Overflow

How to create a pivot table in Postgresql using case when?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related