Group and show averages of a subquery that uses union

Question

Group and show averages of a subquery that uses union

Navigation

#1 by (3 votes)

4

I have a table with the following data ..

INSERT INTO Partido (nro,cod_local,gol_local,cod_visitante,gol_visitante)
VALUES              (1,  'AR',     2,        'CL',         1),
                    (2,  'US',     0,        'CO',         2),
                    (3,  'BR',     7,        'HA',         1),
                    (4,  'MX',     1,        'VE',         1),
                    (5,  'PA',     0,        'AR',         2),
                    (6,  'PE',     0,        'BR',         8),
                    (7,  'CO',     1,        'PR',         0),
                    (8,  'AR',     1,        'VE',         0),
                    (9,  'BR',     1,        'AR',         1),
                    (10, 'MX',     0,        'CR',         0);

What I'm trying to do is get the goal average per game for each team. For example, Argentina has 6 goals in 4 matches (1.5).

In this consultation you can see the goals scored by each team

SELECT tot.cod_local,tot.cantGoles
FROM
(
    SELECT cod_local,gol_local AS cantGoles
    FROM Partido 
    UNION ALL
    SELECT cod_visitante,gol_visitante AS cantGoles
    FROM Partido
) AS tot;

The problem is that this query does not show me the grouped data (it gives them to me separately by party). Following the example of Argentina, in the results of this country I get something like ..

Pais      Goles
AR        2
AR        1
AR        1
AR        2

When I would like it to come out ..

Pais      Goles
AR        6
BR        16
(etcetera)

As it is not grouped (try tot.cod_local but I'm not good at the values) I do not know how to continue to finish getting the average

mysql

asked by mistes 19.06.2016 в 02:43

source

1 answer

Multiple filtering Django Rest Framework Format with decimals

score 3 · Accepted Answer

If they should be grouped to obtain the average for each country, it would be like this:

select tot.codigo_pais, avg (tot.goles)
from (
         select cod_local as codigo_pais, gol_local as goles
         from partido
         union all
         select cod_visitante as codigo_pais, gol_visitante as goles
         from partido
     ) as tot
group by tot.codigo_pais