GROUP BY with CASE expression IN PostgreSQL

Question

This is my query:

SELECT userid,
  case when extract(day from date )=1 then  string_agg(status,'') end "1" ,
  case when extract(day from date )=2 then  string_agg(status,'') end "2" ,
.
.
.
  case when extract(day from date )=31 then string_agg(status,'') end "31"
from attendance.attendance group by userid; 

But I have the next error:

ERROR:  column "attendance.date" must appear in the GROUP BY clause or be used in an aggregate function

When group by userid and date the output:

userid 1 2 3 ... 31
1      P
1        A
1          P ... 
1                P

I want to group by userid like this:

userid 1 2 3 ... 31
1      P A P ... P
2      P P P ... P
etc.

Any ideas? I'll very appreciate.

Answer 1

You'll want to put the string_agg around the CASE expression:

SELECT userid,
  string_agg(case when extract(day from date)=1 then status end) "1" ,
  string_agg(case when extract(day from date)=2 then status end) "2" ,
  .
  .
  .
  string_agg(case when extract(day from date)=31 then status end) "31"
FROM attendance.attendance
GROUP BY userid;

A nicer way to write this query would be using a FILTER for the aggregate:

SELECT userid,
  string_agg(status) FILTER (WHERE extract(day from date)=1) "1" ,
  string_agg(status) FILTER (WHERE extract(day from date)=2) "2" ,
  .
  .
  .
  string_agg(status) FILTER (WHERE extract(day from date)=31) "31"
FROM attendance.attendance
GROUP BY userid;