infuse a sum of the value in the another column with a different filter than the total count column

Question

First here's a sample table.

Provider_name patient       date        status  length
AF          AGUIR00001  07/05/2018  3       30
AF          ABBOT00001  07/05/2018          30
BB          ADAMS00001  07/05/2018  3       30
BB          ACEVE00001  07/06/2018  3       30

I have created a query that lets me count the total number of appointments versus the number of appointments with a certain status(eg checked out). I was able to create it and group it by provider.

select provider_name,
    count(patient) total,
    sum(case when status = 3 then 1 else 0 end) as Checkedout
from appointment
group by provider_name

Then I moved on to the next phase which was to get the total length of those appointments with checkedout status. I made this query but it does not break down into each provider.

select provider_name,
    count(patient) total,
    sum(case when status = 3 then 1 else 0 end) as Checkedout,
    (select sum(length) from appointment where status = 3 
and date between '06/01/2018' and '07/06/2018') 
from appointment where date between '06/01/2018' and '07/06/2018'
group by provider_name

I need it so that the last column in the query is segregated per provider_name.

Thank you in advance for helping me out.

Answer 1

Actually, you were on the right way, try this:

select provider_name,
       count(patient) total,
       sum(case when status = 3 then 1 else 0 end) as Checkedout,
       sum(case when status = 3 then length else 0 end) as len_status3
  from appointment
 where date between '2018-01-06' and '2018-06-07'
 group by provider_name;

According to your last comment, you need a WITH ROLLUP modifier for GROUP BY as in the following :

select coalesce(provider_name,'Total') as provider_name,
       count(patient) total,
       sum(case when status = 3 then 1 else 0 end) as Checkedout,
       sum(case when status = 3 then length else 0 end) as len_status3
  from appointment
 where date between '2018-01-06' and '2018-06-07'
 group by provider_name with rollup;

SQL Fiddle Demo