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