select distinct data from postgres table

Question

I have a table like this:

I would like a query to return results similar to this:

I would like all the sales values displayed througout the table. Also, I would like the null results to be displayed in the 'other' row, is this possible? I am new to postgres and having a hard time trying to achieve this. Any help is appreciated.

Stefanov.sm · Accepted Answer · 2022-04-08 14:55:39Z

1

First coalesce nulls in device_type to 'other',
then group by coalesce(device_type, 'other') and use conditional sums with filter clause.

select coalesce(device_type, 'other') as device,
       sum(sales) filter (where group_name = 'GROUP_A') as group_a,
       sum(sales) filter (where group_name = 'GROUP_B') as group_b,
       sum(sales) filter (where group_name = 'GROUP_C') as group_c,
       sum(sales) filter (where group_name = 'GROUP_D') as group_d
from the_table
group by coalesce(device_type, 'other');

Instead of group by coalesce(device_type, 'other') you can write group by 1 i.e. by the first expression in the select list. It is shorter but I am not sure that it is better readable.

edited Apr 8, 2022 at 14:55

answered Apr 8, 2022 at 14:48

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

2 Comments

cozzymotto Over a year ago

question, what if I wanted the group_name columns created dynamically? Example, if I were to run the same query on area2 with the device_types still the same but different types of groups, how would that work?

Stefanov.sm Over a year ago

Well, this would change the resulting table structure dynamically. I do not think that this is supported or that it is a good idea. But still you could combine all the group_name columns into one JSON column.

Collectives™ on Stack Overflow

select distinct data from postgres table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related