Question 191048 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Group records by selected values into different columns

I have following example:

create table products (name text, id integer, type smallint, category integer);

insert into products values 
('apple', 23, 1, 1200),
('apple', 23, 1, 999),
('apple', 31, 2, 1200),
('apple', 23, 3, 1200),
('orange', 10, 1, 200),
('orange', 10, 2, 200),
('orange', 10, 2, 230),
('orange', 10, 3, 200),
('orange', 64, 1, 700),
('orange', 70, 2, 700);

DB FIDDLE

I want the results to be like this:

name type_1_3_id type_2_id
apple 23 31
orange {10,64} {10,70}

But postgres forces me to group by column type, which makes my results not as I want them to be

select distinct name, 
case when type in (1,3) then array_agg(distinct id) end as type_1_3,
case when type = 2 then array_agg(distinct id) end as type_2
from products
group by 1, type
order by 1
Tags:

2

Answers


  1. 0

    Since you want DISTINCT values and also you want to exclude nulls, you can’t use ARRAY_AGG() out of the box. You can filter and aggregate separately and then join these aggregations.

    For example:

    select coalesce(x.name, y.name) as name, x.a as type_1_3, y.a as type_2
from (
  select name, array_agg(distinct id) as a 
  from products where type in (1, 3) group by name
) x
full join (
  select name, array_agg(distinct id) as a 
  from products where type = 2 group by name
) y on y.name = x.name

    Result:

     name    type_1_3  type_2  
 ------- --------- ------- 
 apple   {23}      {31}    
 orange  {10,64}   {10,70}

    See running example at db<>fiddle.

    Login or Signup to reply.
  2. 0

    Use the filter clause with your array_agg function:

    select name, 
 array_agg(distinct id) filter (where type in (1,3)) as type_1_3,
 array_agg(distinct id) filter (where type = 2) as type_2
from products
group by 1
order by 1

    With conditional aggregation array_agg(case when... then ... end) a null value is returned for the unmatched rows, while filters would aggregate only the specified rows within the where clause.

    If FILTER is specified, then only the input rows for which the filter_clause evaluates to true are fed to the aggregate function; other rows are discarded.

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search