select distinct data from postgres table-CodePudding

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.

CodePudding user response：

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.