Question: Getting Hourly Average


Getting Hourly Average

Answers 1
Added at 2017-01-04 04:01

I'm fairly new to coding and would appreciate all the help I can get. I am trying to get hourly amount values from my transaction table. It collects data at various times. I would like to get the hourly average per each cashier ID with the output showing the start of the hour. Additionally, my table is rather large so I am trying to only get the hourly averages between a particular date. Here is a snippet of my table:

         November Transactions      
Cash_ID Trans_Time          Amount
1       11/01/16 12:00:00PM   5.08  
1       11/01/16 12:03:00PM   8.98
2       11/01/16 12:05:00PM  38.53
3       11/01/16 12:41:00PM  12.91
1       11/01/16 12:11:00PM  14.55
3       11/01/16 01:00:00PM  17.74
2       11/01/16 12:45:00PM  21.33
3       11/01/16 01:07:00PM  33.51
2       11/01/16 12:15:00PM  15.86

This is what I am trying to achieve.

    November Transactions       
Cash_ID  Trans_Time           Amount
1        11/01/16 12:00:00PM    9.54
2        11/01/16 12:00:00PM   25.24
3        11/01/16 12:00:00PM   12.91
3        11/01/16 01:00:00PM   25.63

I figured out how to filter the trans_time by the hour but am having difficulties calculating the average. Currently I have

SELECT Cash_ID, Trans_Time, Amount
FROM November Transactions
WHERE Trans_Time between to_date('2016/11/01', 'yyyy/mm/dd hh:mi:ss')AND to_date('2016/11/02', 'yyyy/mm/dd hh:mi:ss')
AND Trans_Time in
(Select Trans_Time From November Transactions
 where Trans_Time = Trunc(Trans_Time,'hh')) 

I looked at other questions similar to mine but they all only used the count function for hourly values. I tried AVG(Amount) in place of Amount in the select statement but got an "not a single-group group function" error. I was thinking maybe another inner select but couldn't figure out what the conditions would be. Please correct what I have so far if it's wrong as well. Thanks.

nr: #1 dodano: 2017-01-04 04:01

I think you are on the right track. You just need the right aggregation and some other fixes to the logic:

SELECT Cash_ID, trunc(Trans_Time, 'hh'), AVG(Amount)
FROM November_Transactions
WHERE Trans_Time >= date '2016-11-01' and
      Trans_Time < date '2016-11-02'
GROUP BY Cash_ID, trunc(Trans_Time, 'hh')
ORDER BY Cash_ID, trunc(Trans_Time, 'hh');

Note: I changed the logic for the between, because I don't want to include times at midnight in the day.

Source Show
◀ Wstecz