0

Below is my SQL Query... My Averages however are not averaging the totals, but rather just displaying the SUM. I am not sure why though. Can anyone provide any insight?

   SELECT
AVG(a.t1) as '8:00-9:00',
AVG(a.t2) as '9:00-10:00',
AVG(a.t3) as '10:00-11:00',
AVG(a.t4) as '11:00-12:00',
AVG(a.t5) as '12:00-1:00',
AVG(a.t6) as '1:00-2:00',
AVG(a.t7) as '2:00-3:00',
AVG(a.t8) as '3:00-4:00',
AVG(a.t9) as '4:00-5:00',
AVG(a.t10) '5:00-6:00',
AVG(a.t11) as '6:00-7:00',
AVG(a.t12) as '7:00-8:00' 
from 
(select COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('07:00:00' AS time) AND CAST('08:00:00' AS time) then 1 end) as t1,
 COUNT(Case when CAST(Request_Datetime AS time)BETWEEN CAST('08:00:00' AS time) AND CAST('09:00:00' AS time) then 1 end) as t2,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('10:00:00' AS time) AND CAST('11:00:00' AS time) then 1 end) as t3,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('11:00:00' AS time) AND CAST('12:00:00' AS time) then 1 end) as t4,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('12:00:00' AS time) AND CAST('13:00:00' AS time) then 1 end) as t5,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('13:00:00' AS time) AND CAST('14:00:00' AS time) then 1 end) as t6,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('14:00:00' AS time) AND CAST('15:00:00' AS time) then 1 end) as t7,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('15:00:00' AS time) AND CAST('16:00:00' AS time) then 1 end) as t8,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('16:00:00' AS time) AND CAST('17:00:00' AS time) then 1 end) as t9,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('17:00:00' AS time) AND CAST('18:00:00' AS time) then 1 end) as t10,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('18:00:00' AS time) AND CAST('19:00:00' AS time) then 1 end) as t11,
 COUNT(Case when CAST(Request_Datetime AS time) BETWEEN CAST('19:00:00' AS time) AND CAST('20:00:00' AS time) then 1 end) as t12 ,
 COUNT(Interaction_ID) as dayCount
 from rt_queue_delta 
 Where DateName( dw,Request_datetime) in('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') and  
 CAST(Request_datetime as Date) >= '05/01/2015' and Department = 1) as a
Robert Louis Everhard II
  • 61
  • 1
  • 6

2 Answers2

0

You have an implicit GROUP BY doing the COUNTs in the subquery and then you AVG over single values (the COUNTs), so basically in your query the AVGs are no-ops.

Throw away the outer query and calculate AVGs instead of COUNTs in the subquery:

COUNT(CASE WHEN VAL BETWEEN... THEN 1 END)

should become

AVG(CASE WHEN VAL BETWEEN... THEN VAL END)

gpeche
  • 21,974
  • 5
  • 38
  • 51
0

I suspect that the query that you want is:

select AVG(Case when CAST(Request_Datetime AS time) BETWEEN CAST('07:00:00' AS time) AND CAST('08:00:00' AS time) then 1.0 else 0 end) as t1,
       . . .
       AVG(Case when CAST(Request_Datetime AS time) BETWEEN CAST('19:00:00' AS time) AND CAST('20:00:00' AS time) then 1.0 else 0 end) as t12 ,
       COUNT(Interaction_ID) as dayCount
 from rt_queue_delta 
 Where DateName(dw, Request_datetime) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') and  
       CAST(Request_datetime as Date) >= '2015-05-01' and
       Department = 1;

This will return the proportion of the total in each time period. Note the use of 1.0 and else 0.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The numbers don't seem to come out correctly doing this. ideally i need to do AVG(SUM(things to sum)) but this isnt allowed. That was why I initially did an outside query – Robert Louis Everhard II Jun 02 '16 at 12:38
  • @RobertLouisEverhardII . . . I don't see why the `SUM()` would be necessary. Perhaps sample data and desired results would help the question. – Gordon Linoff Jun 03 '16 at 02:30