i have 3 columns named: event_status varchar, event_site varchar, payor varchar. i am using pgadmin. event _status contain 2 unique values ('show' and 'no show'), event_site contains (name of several coutries), and payor contains( names of several company). what i need is to have count of show and no show in different columns grouped by event_site and payor. any help is appreciated.
select event_site, payor, name_policy, sum(item_qty) as dsh, count(distinct event_id) as ttl_show,
count(distinct pres_ind_id) as unique_served,
(select count(distinct event_id) from cart_item_funder_policy_worker where event_status like 'No%') as no_show
from cart_item_funder_policy_worker
where date_part('year', event_time) = 2022 and
event_status like 'Show%'
group by event_site, payor, name_policy
order by year_ Desc;