# How to calculate different avg for the same record?

• A+
Category：Languages

I have a list of `venue` where different `teams` can play a `match`. Now, a `team` for each `match` can get an `attendance` for the `venue`, so I'm trying to calculate the `AVG` of the `attendance`, the problem is that I get the result grouped for one `venue`, that's because I used an aggregate function and I'm forced to use `GROUP BY`, the query should return instead the same `venue` but for different `teams` and with different `AVG attendance` of course.

Query

``SELECT m.venue_id, MIN(m.venue_attendance) AS min_attendance, MAX(m.venue_attendance) AS max_attendance, SUM(m.venue_attendance) AS venue_sum, v.name AS venue_name, ROUND(AVG(m.venue_attendance), 2) AS average, v.capacity, t.name AS team_name FROM `match` m INNER JOIN venue v ON v.id = m.venue_id INNER JOIN team_info i ON i.venue_id = m.venue_id INNER JOIN team t ON t.id = i.team_id WHERE m.round_id = :round_id GROUP BY m.venue_id, t.name ORDER BY average DESC ``

Data Sample

match

``id      | round_id | home_team_id | away_team_id | venue_id | venue_attendance 2506177     28            70             71           10           6000 2506195     28            70             76           10            500 2506204     28            70             69           10           2000 2506219     28            70             72           10            500 2506230     28            70           2517           10            300 2506235     28            70           2522           10            500 2506244     28            70          10049           10            400 2506252     28            70          12573           10           6000 2506258     28          2518             70           10           4500 2506267     28            70             71           10           1000 2506285     28            70             76           10            700 2506294     28            70             69           10           1500 2506303     28            70           2518           10           2500 2506309     28            70             72           10           1200 2506320     28            70           2517           10           1200 2506325     28            70           2522           10            800 2506334     28            70          10049           10           5500 2506342     28            70          12573           10           1000 ``

venue

``id | name                 | address          | zip_code | city    | phone | 10   Stadiumi Loro Boriçi  Rruga Musa Luli 1    4000      Shkodër   NULL ``

team_info

``team_id | venue_id |   70         10  2518         10 ``

team

``  id | name    70   Skënderbeu Korçë 2518   Vllaznia Shkodër ``

Output

``{     "venue_id": "10",     "min_attendance": "300",     "max_attendance": "6000",     "venue_sum": "36100",     "venue_name": "Stadiumi Loro Boriçi",     "average": "2005.56",     "capacity": "16000",     "team_name": "Vllaznia Shkodër" } ``

Expected output

``{     "venue_id": "10",     "min_attendance": "300",     "max_attendance": "6000",     "venue_sum": "31600",     "venue_name": "Stadiumi Loro Boriçi",     "average": "1858",     "capacity": "16000",     "team_name": "Vllaznia Shkodër" }, {     "venue_id": "10",     "min_attendance": "4500",     "max_attendance": "4500",     "venue_sum": "4500",     "venue_name": "Stadiumi Loro Boriçi",     "average": "4500",     "capacity": "16000",     "team_name": "Skënderbeu Korçe" } ``

As you can see the `venue_sum` should be calculated only when the `team` play in home, looking at the field `home_team_id`, and the average is the total of the `venue_sum / matches played by the team`, for example, for `Vllaznia Shkodër` we have as average: 31600 / 17 = 1858.

Full database: https://files.fm/u/2xwgkaxz

For access the data in the example, simply run:

``SELECT * FROM `match` WHERE round_id = 28 ``

How can I handle this?

the solution proposal by scaisEdge works only for elaboration result, infact now the average is correct. The main problem still persist, infact the actual result of scaisEdge query is this:

``{     "venue_id": "10",     "min_attendance": "300",     "max_attendance": "6000",     "venue_sum": "31600",     "venue_name": "Stadiumi Loro Boriçi",     "average": "1858.82",     "capacity": "16000",     "team_name": "Vllaznia Shkodër" }  ``

there are also other venues, but I want focalize the attention on this, as I said before I need to return the same venue for different teams, so I should also get:

``{     "venue_id": "10",     "min_attendance": "4500",     "max_attendance": "4500",     "venue_sum": "4500",     "venue_name": "Stadiumi Loro Boriçi",     "average": "4500",     "capacity": "16000",     "team_name": "Skënderbeu Korçe" } ``

but I get only:

``{     "venue_id": "10",     "min_attendance": "300",     "max_attendance": "6000",     "venue_sum": "31600",     "venue_name": "Stadiumi Loro Boriçi",     "average": "1858.82",     "capacity": "16000",     "team_name": "Vllaznia Shkodër" }  ``

so the expected final result must include this:

``{     "venue_id": "10",     "min_attendance": "300",     "max_attendance": "6000",     "venue_sum": "31600",     "venue_name": "Stadiumi Loro Boriçi",     "average": "1858.82",     "capacity": "16000",     "team_name": "Vllaznia Shkodër" }, {     "venue_id": "10",     "min_attendance": "4500",     "max_attendance": "4500",     "venue_sum": "4500",     "venue_name": "Stadiumi Loro Boriçi",     "average": "4500",     "capacity": "16000",     "team_name": "Skënderbeu Korçe" } ``

You are joining the `team_info` table using only `venue_id`. This way every row from `match` will be joined with every team in `team_info`. You should add the condition `i.team_id = m.home_team_id` to limit the JOIN to the home team only:

``SELECT m.venue_id, MIN(m.venue_attendance) AS min_attendance, MAX(m.venue_attendance) AS max_attendance, SUM(m.venue_attendance) AS venue_sum, v.name AS venue_name, ROUND(AVG(m.venue_attendance), 2) AS average, -- v.capacity, -- no such column in sample data t.name AS team_name FROM `match` m INNER JOIN venue v ON v.id = m.venue_id INNER JOIN team_info i    ON  i.venue_id = m.venue_id   AND i.team_id  = m.home_team_id -- this is the fix INNER JOIN team t ON t.id = i.team_id WHERE m.round_id = 28 GROUP BY m.venue_id, t.name ORDER BY average DESC ``

Result:

``| venue_id | min_attendance | max_attendance | venue_sum |           venue_name | average |        team_name | |----------|----------------|----------------|-----------|----------------------|---------|------------------| |       10 |           4500 |           4500 |      4500 | Stadiumi Loro Boriçi |    4500 | Vllaznia Shkodër | |       10 |            300 |           6000 |     31600 | Stadiumi Loro Boriçi | 1858.82 | Skënderbeu Korçë | ``

But you can probably just skip the `team_info` table and join the `team` table directly to `match`:

``SELECT m.venue_id, MIN(m.venue_attendance) AS min_attendance, MAX(m.venue_attendance) AS max_attendance, SUM(m.venue_attendance) AS venue_sum, v.name AS venue_name, ROUND(AVG(m.venue_attendance), 2) AS average, -- v.capacity, -- no such column in sample data t.name AS team_name FROM `match` m INNER JOIN venue v ON v.id = m.venue_id INNER JOIN team t ON t.id = m.home_team_id  -- this is the fix WHERE m.round_id = 28 GROUP BY m.venue_id, t.name ORDER BY average DESC ``

The result is the same.