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.
Thx
