Join 2 different counts from 2 different tables into one subtable in sql-CodePudding

I'm having a problem in where i want to count how many medals in total a country has won from both the individual and team competitions does not give me the disered outcome. i have managed so far tocome up with this.

select distinct C.Cname as Country, count(i.medal) as Medals_Won
from individual_results as i, Country as C, participant as p 
where (i.Olympian = p.OlympicID and C.Cname = p.country) 

union 

select distinct C.Cname, count(r.medal) as medals_Won
from team_results as r, Country as C, participant as p, team as t
where (r.team = t.TeamID and t.Member1 = p.OlympicID and C.Cname = p.Country)


group by C.Cname
order by medals_won desc

enter image description here

but i get this result.

even tho if i run the two separate pieces of code i ge the wanted restuls that is enter image description here

CodePudding user response：

You should sum the union result for each of the subquery grouped by cname

    select t.Cname , sum( t.Medals_Won)
    from (

        select  C.Cname as Country, count(i.medal)  Medals_Won
        from individual_results  i 
        inner join  participant  p  ON i.Olympian = p.OlympicID 
        inner join Country  C ON C.Cname = p.country
        group by C.Cname

        union 

        select distinct C.Cname, count(r.medal) 
        from team_results as r
        inner join team as t ON r.team = t.TeamID
        inner join  participant as p ON t.Member1 = p.OlympicID
        inner join Country as C ON C.Cname = p.Country
        group by C.Cname

    ) t 
    group by t.Cname
    order by t.medals_won desc

CodePudding user response：

You say you can run your query and it gives you a result. This is bad. It indicates that you are MySQL's notorious cheat mode that lets you run invalid queries.

You have something like this:

select ...
union
select ...
group by ...
order by ...

There are two queries the results of which you glue together, namely

select ...

and

select ...
group by ...

So, your first query becomes:

select distinct C.Cname as Country, count(i.medal) as Medals_Won
from individual_results as i, Country as C, participant as p 
where (i.Olympian = p.OlympicID and C.Cname = p.country)

You COUNT medals, i.e. you aggregate your data. And there is no GROUP BY clause. So you get one result row from all your data. You say you want to count all rows for which i.medal is not null. But you also want to select the country. The country? Which??? Is there just one country in the tables? And even then your query would be invalid, because still you'd have to tell the DBMS from which row to pick the country. You can pick the maximum country (MAX(C.Cname)) for instance or the minimum country (MIN(C.Cname)), but not the country.

The DBMS should raise an error on this invalid query, but you switched that off.

Make sure in MySQL to always

SET sql_mode = 'ONLY_FULL_GROUP_BY';

It is the default in more recent versions, so either you are working with old software or you switched from good mode to bad mode voluntarily.

And talking of old software: Even at the first moment MySQL was published, comma joins had long been deprecated. They were made redudant in 1992. Please don't ever use commas in your FROM clause. Use explicit joins ([INNER] JOIN, LEFT [OUTER] JOIN, etc.) instead.

As to the task, here is a straight-forward solution with joins:

select 
  c.cname as country, 
  coalesce(i.medals, 0) as medals_individual,
  coalesce(t.medals, 0) as medals_team,
  coalesce(i.medals, 0)   coalesce(t.medals, 0) as medals_total
from country c
left outer join 
(
  select p.country, count(ir.medal) as medals
  from participant p 
  join individual_results ir on ir.olympian = p.olympicid
  group by p.country
) i on on i.country = c.name
left outer join 
(
  select p.country, count(ir.medal) as medals
  from participant p 
  join team t on t.member1 = p.olympicid
  join team_results tr on tr.team = t.teamid
  group by p.country
) t on on t.country = c.name
order by medals_total desc;