SQL Query Count

Question

SQL Query Count

Navigation

#1 by (2 votes)

6

I need to make a query where 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 the medals obtained in collective events (courier, etc.) can only be counted 1 time, ie In the case of a coupon event instead of counting 4 medals count only 1.

You can tell if an event is a collective or an individual event using the events table in the WINDOWS field, because this field is either Individual or Collective.

I think it's possible to do it through a Subquery with a count (distinct) but I do not know how.

Diagram:

Result:

    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

sql query

asked by anonymous 20.05.2014 / 00:40

1 answer

Name of the months in Portuguese and English Why does ob_get_clean clear the memory without starting the buffer with ob_start ()?

score 2 · Accepted Answer

What happens is that when doing the inner join with the table participants it associating a row of the result table for each row of the table participants.

If the idea is to return the totals of medals because they do not put directly without including all the tables?

Another thing to make easier is not to put the SIGLAPAL field in the RESULTS table.

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