6
I need to make a query in which the result is the medals (gold, silver, bronze and total (sum of 3)) obtained by each country in all editions of the Olympic Games. So far so good, the query below already does this.
The problem is that medals obtained at collective events(courier,etc) can only be counted 1 time, ie , In the case of a courier event instead of counting 4 medals count only 1.
It is possible to know if an event is of the collective or individual type through the table events in the field TYPOEVENTO, because this field is either Individual or Collective.
I think it is possible to do through a Subquery with a Count(distinct) but I don’t know how.
Diagram:
Upshot:
Select PAISES.NOMEPAIS, count(RESULTADOS.CLASSIFICACAO) as Total,
Count(Case when RESULTADOS.CLASSIFICACAO = 1 then 1 end) As OURO,
Count(Case when RESULTADOS.CLASSIFICACAO = 2 then 1 end) As PRATA,
Count(Case when RESULTADOS.CLASSIFICACAO = 3 then 1 end) As BRONZE
From
RESULTADOS
INNER JOIN PARTICIPACOES
ON RESULTADOS.ANO = PARTICIPACOES.ANO AND RESULTADOS.ESTACAO = PARTICIPACOES.ESTACAO AND RESULTADOS.IDPARTICIPACAO = PARTICIPACOES.IDPARTICIPACAO
INNER JOIN dbo.PAISES
ON PARTICIPACOES.SIGLAPAIS = PAISES.SIGLAPAIS
INNER JOIN dbo.PARTICIPANTES
ON PARTICIPACOES.IDPARTICIPANTE = PARTICIPANTES.IDPARTICIPANTE
INNER JOIN dbo.EDICOES
ON PARTICIPACOES.ANO = EDICOES.ANO AND PARTICIPACOES.ESTACAO = EDICOES.ESTACAO
INNER JOIN dbo.ELIMINATORIAS
ON RESULTADOS.IDEVENTO = ELIMINATORIAS.IDEVENTO AND RESULTADOS.IDELIMINATORIA = ELIMINATORIAS.IDELIMINATORIA
group by NOMEPAIS
order by Total desc
SELECT DISTINCT PAISES.NOMEPAIS AS NOMEPAIS, RESULTADOS.CLASSIFICACAO,
EVENTOS.IDEVENTO, EVENTOS.TIPOEVENTO , PARTICIPACOES.ANO,Count(Case when RESULTADOS.CLASSIFICACAO = 1 then 1 end) As OURO,
Count(Case when RESULTADOS.CLASSIFICACAO = 2 then 1 end) As PRATA,
Count(Case when RESULTADOS.CLASSIFICACAO = 3 then 1 end) As BRONZE,
count(RESULTADOS.CLASSIFICACAO) as Total
FROM RESULTADOS
INNER JOIN dbo.PARTICIPACOES
ON RESULTADOS.IDPARTICIPACAO = PARTICIPACOES.IDPARTICIPACAO
INNER JOIN dbo.PAISES
ON PARTICIPACOES.SIGLAPAIS = PAISES.SIGLAPAIS
INNER JOIN dbo.EVENTOS
ON RESULTADOS.IDEVENTO = EVENTOS.IDEVENTO
group by RESULTADOS.CLASSIFICACAO, NOMEPAIS , EVENTOS.IDEVENTO, TIPOEVENTO, Participacoes.ANO
For those who, like me, ask themselves what a courier is, this is what in Brazil we call the pole relay race.
– bfavaretto
I want to help you with this. I could set up the basic structure and the Insert on this site http://www.sqlfiddle.com and pass the URL, then help you to build SQL
– Tiedt Tech
The sqlfiddle.com website is extremely slow. If you have microsoft sql server installed, here I leave the backup of this database, https://drive.google.com/file/d/0B-jRMXMu9WvfNzkyeTk0SlBVTkU/edit
– user3653161
Since it is a counting query only take PARTICIPANTS (athletes, I believe) from JOIN, only medals would be counted, whether individual or collective.
– Motta
Thanks for the help, but that’s not it. It still counts the medals of individual collective events, when it should count only 1 time.
– user3653161