CODEWITHSUNDEEP
sqlpostgresql
user1731940
user1731940

Reputation: 41

Sql time range count with average and formula

SQl query return the particular range of time values and count

select
res.range, count(*)
from (select 
CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours' 
WHEN t1.response_time between 4 and 6 then '4-6 Hours' 
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then  '8+ Hours'
END as range
from table1 t1) as res
group by res.range

The below SQl query return the particular range of time values and count

   Range        | Count
 ----------------------
  4-6 Hours     |  24
  8+ Hours      |  23
  6-8 Hours     |  22
  Within 4 Hours|  7

Expected Output

  Range        | Count
----------------------
 4-6 Hours     |  24
 8+ Hours      |  23
 6-8 Hours     |  22
 Within 4 Hours|  7
 Average       |  x  (Average of Range Time Count)

In the range last row i want to shows the average and average range time count. How to display the last row average and also what is the formula for calculating Average of Range Time count.

Upvotes: 0

Views: 269

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

Try this

select
res.range, count(*)
from (select 
CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours' 
WHEN t1.response_time between 4 and 6 then '4-6 Hours' 
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then  '8+ Hours'
END as range
from table1 t1) as res
group by res.range
union all
select 'Average', AVG(counting) from
(
select
res.range, count(*) as counting
from (select 
CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours' 
WHEN t1.response_time between 4 and 6 then '4-6 Hours' 
WHEN t1.response_time between 6 and 8 then '6-8 Hours'
WHEN t1.response_time > 8 then  '8+ Hours'
END as range
from table1 t1) as res
group by res.range
) t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this with a with rollup trick:

select coalesce(res.range, 'Total') as range, avg(cnt) as cnt
from (select res.range, count(*) as cnt
      from (select (CASE WHEN t1.response_time between 0 and 4 then 'Within 4 Hours' 
                         WHEN t1.response_time between 4 and 6 then '4-6 Hours' 
                         WHEN t1.response_time between 6 and 8 then '6-8 Hours'
                         WHEN t1.response_time > 8 then  '8+ Hours'
                    END) as range
           from table1 t1
          ) res
      group by res.range
     ) r
group by res.range with rollup;

The outer group by sort of does nothing for the existing rows -- the average of a single value is that value. But it should the total line with the average.

Upvotes: 0

Related Questions