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?

scaisEdge answer:

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çë | 

Fiddle: http://sqlfiddle.com/#!9/9aaf7c9/1

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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: