'How to calculate daily average frequency in a table in function of GROUP BY from another table?
I want to know which cinema room is more frequented daily and I need the values as relative frequency.
My tables are:
table1:
| client_id | cinema_room |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | C |
| 5 | A |
| 6 | C |
| 7 | B |
| 8 | A |
| 9 | B |
| 10 | B |
| 11 | B |
| 12 | A |
| 13 | C |
| 14 | A |
| 15 | A |
| 16 | B |
Table 2:
| day | client_id |
|---|---|
| 01/01/2022 | 1 |
| 01/01/2022 | 1 |
| 01/02/2022 | 1 |
| 01/02/2022 | 2 |
| 01/02/2022 | 4 |
| 01/03/2022 | 8 |
| 01/04/2022 | 14 |
| 01/04/2022 | 15 |
| 01/04/2022 | 16 |
So I need results like:
| day | cinema_room | avg_freq |
|---|---|---|
| 01/01/2022 | A | 1 |
| 01/01/2022 | B | 0 |
| 01/01/2022 | C | 0 |
| 01/02/2022 | A | 0.33 |
| 01/02/2022 | B | 0.33 |
| 01/02/2022 | C | 0.33 |
| 01/03/2022 | A | 1 |
| 01/03/2022 | B | 0 |
| 01/03/2022 | C | 0 |
| 01/04/2022 | A | 0.66 |
| 01/04/2022 | B | 0.33 |
| 01/04/2022 | C | 0 |
What I've got by now is:
SELECT day , cinema_room, COUNT(t2.client_id) as t2_tot
FROM table1 t1
LEFT JOIN table2 t2 ON t1.client_id = t2.client_id
GROUP BY day, cinema_room
ORDER BY day
Its insufficient results are:
NOTE 1: I'm counting, not even averaging.
NOTE 2: The first three rows are wrong af.
| day | cinema_room | count_freq |
|---|---|---|
| None | A | 0 |
| None | B | 0 |
| None | C | 0 |
| 01/01/2022 | A | 3 |
| 01/01/2022 | B | 0 |
| 01/01/2022 | C | 0 |
| 01/02/2022 | A | 1 |
| 01/02/2022 | B | 1 |
| 01/02/2022 | C | 1 |
| 01/03/2022 | A | 1 |
| 01/03/2022 | B | 0 |
| 01/03/2022 | C | 0 |
| 01/04/2022 | A | 2 |
| 01/04/2022 | B | 1 |
| 01/04/2022 | C | 0 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|