SQL Query count

Question

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

Answer 1

What happens is that when doing the Inner Join with the table participants he associating a row of the table result for each row of the table participants.

If the idea is to return the medals totals why not put directly without including all tables?

Another thing, to facilitate why not put in the table RESULTS the SIGLAPAIS field.

Select A.NOMEPAIS, count(A.RESULTADO) as Total,
Count(Case when A.RESULTADO = 1 then 1 end) As OURO,
Count(Case when A.RESULTADO = 2 then 1 end) As PRATA,
Count(Case when A.RESULTADO= 3 then 1 end) As BRONZE
From
(
   SELECT DISTINCT PAISES.NOMEPAIS AS NOMEPAIS, RESULTADOS.CLASSIFICACAO AS RESULTADO, EVENTOS.IDEVENTO AS IDEVENTO, EVENTOS.TIPOEVENTO AS TIPOEVENTO, PARTICIPACOES.ANO AS ANO
   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

) AS A
group by A.NOMEPAIS
order by Total desc