'Report an amount field by a value in another field

I have a large table that has a structure something like the following:

Create Table public.example
(
id character varying(7) ,
type character varying(1) ,
amount decimal ,
date1 date ,
plan  character varying(2) ,
date2 date  );

Insert into public.example
( Id,Type,Amount,Date1,Plan,Date2)
values 
( '1343657' , 'e',235.26 ,'2021-01-03', 'HS', '2021-07-03'),
( '1343657' , 's',6234.25,'2021-01-15', 'RT', '2021-05-09'),
( '1343657' , 's',235.26 ,'2021-01-05', 'HS', '2021-05-03'),
( '1343657' , '3',235.26 ,'2021-01-05', 'HS', '2021-05-17'),
( '1343657' , 's',235.26 ,'2021-01-05', 'HS', '2021-03-19'),
( '5364324' , 'e',1245.90,'2021-01-17', 'MM', '2021-04-23'),
( '5364324' , '1',5285.88,'2021-01-14', 'MM', '2021-02-28'),
( '5364324' , 'e',1245.10,'2021-01-08', 'VI', '2021-06-30'),
( '5364324' , 'e',7452.05,'2021-01-10', 'DT', '2021-03-07') ;

I need to list the "amount" field across the report in different buckets based on the value of the “Plan” field. I also need to summarize the amount by Id and Type. My method doesn’t work because it adds another required Group BY and I don’t get a summarized amount by Id and Type.

Select id,type,
case    When a.plan ='HS' then sum(amount) else 0 end as "HS",
case    When a.plan ='RT' then sum(amount) else 0 end as "RT",
case    When a.plan ='MM' then sum(amount) else 0 end as "MM",
case    When a.plan ='VI' then sum(amount) else 0 end as "VI",
case    When a.plan ='DT' then sum(amount) else 0 end as "DT"
from public.example a
where date2>='2021-01-01' and date2<='2021-12-31'
group by 1,2,a.plan

The perfect solution would allow me to add date1 to the Select output as well. Select Output Select OUTPUT

Thx



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source