I am starting with SQL and I have a problem grouping 2 columns. I need the counting to be filtered by another column, but I obtain the count of all the rows as if they weren’t grouped.

I have 4 different tables with countries, cities, matches and stadiums. I have to obtain names and codes of countries and cities as well as the number of referees that worked in each city and the total number of goals made in every city. I have done this, but I obtain the total number of referees that have worked in the tournament and the total number of goals made during the tournament:

SELECT ci.city_code, 
       ci.city_name, 
       co.country_code, 
       co.country_name, 
       COUNT(DISTINCT referee_code),
       SUM(home_goals+visitor_goals)
FROM euro2021.tb_city AS ci
INNER JOIN  euro2021.tb_country AS co ON  ci.country_code=co.country_code
INNER JOIN  euro2021.tb_match AS m ON ci.city_name=ci.city_name
INNER JOIN  euro2021.tb_stadium AS st on st.stadium_code=m.stadium_code   
GROUP BY ci.city_code, ci.city_name, co.country_code, co.country_name

Do you have any ideas or do you know if this was answered previously? Thanks in advance.

More
sql