A simplified version of my table looks like this:
| id | hour |
|---|---|
| 1 | 3 |
| 1 | 3 |
| 1 | 3 |
| 1 | 4 |
| 2 | 12 |
| 2 | 12 |
| 2 | 14 |
The hour column means 3am, 4am, 12pm etc. What I’d like to be able to do (working in Amazon Athena) is to calculate the average number of times an id occurs per hour. So for id 1, I’d have 3 occurrences at 3am and 1 at 4am, making my average calculation (3+1)/2 = 2 per hour. My combination of count and group by operators are not getting the job done at the moment.
Output I want:
| id | average per hour |
|---|---|
| 1 | 2 |
| 2 | 1.5 |
>Solution :
You can divide the total count per id by the distinct count of hours:
SELECT id, COUNT(*) / COUNT(DISTINCT hour)
FROM mytable
GROUP BY id