using postgresql group by how do i create 2 or more columns from one column-CodePudding

I am using PG admin to run a query. i have (for e.g.) 3 column as below in table named tbl.

| Payor | Event_id| event_Status|
|-------|---------|-------------|
| canada| 100     | show        |
|greenland|200    | no show     |
|canada | 300     | no show     |
|Italy  | 400     | show        |

I need to create a query that give a table like below

| Payor | count(distinct Event_id))| count(event_Status= show))|count(event_Status= no show))|
|-------|--------------------------|---------------------------|-----------------------------|
| canada| 2                        |   1                       |1                            |
|greenland|1                       |   0                       |1                            |
|Italy  |   1                      | 1                         |0                            |

I have this query to return as below, but it does return anything:

select 
    payor, 
    (select count(distinct event_id) 
     from tabl 
     where event_Status like 'Show%' 
     group by payor) as sh,
    (select count(event_status) 
     from tabl 
     where event_Status like 'Show%' 
     group by payor) as sts,
    (select count(distinct event_id) 
     from tabl 
     where event_Status like 'No Show%' 
     group by payor) as ns
    (select count(event status) 
     from tabl 
     where event_Status like 'No Show%' 
     group by payor) as nsts
from tabl;

Any help is appreciated

CodePudding user response：

You can use conditional aggregation. Also, I would avoid using "like" if at all possible. Based on your sample data, you would not match "show" to "Show" unless you use the ilike function, or force the case.

select payor, 
 count(distinct event_id) as events, 
 sum(case when event_status = 'show' then 1 else 0 end) as events_show, 
 sum(case when event_status = 'no show' then 1 else 0 end) as events_no_show
from my_table
group by 1

Fiddle found here.

EDIT:

Based on your comment below, you are wondering about subqueries too. I typically use CTEs, which could look like this:

with e_show as (
  select payor, 
  count(event_id) as events
  from my_table
  where event_status = 'show'
  group by 1
  ),
  e_no_show as (
    select payor, 
    count(event_id) as events
    from my_table
    where event_status = 'no show'
    group by 1
    )
select t.payor, 
 count(distinct event_id) as events, 
 es.events as events_show, 
 ens.events as events_no_show
from my_table t
left join e_show es
   on t.payor = es.payor
left join e_no_show ens
   on t.payor = ens.payor
group by 1,3,4;