SQL Query count

Asked

Viewed 1,248 times

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:

![Diagrama](http://imgur.com/vyhA2Mh)http://imgur.com/vyhA2Mh

Upshot:

![Resultado](http://imgur.com/B69iT6q)http://imgur.com/B69iT6q

    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
  • 2

    For those who, like me, ask themselves what a courier is, this is what in Brazil we call the pole relay race.

  • 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

  • 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

  • Since it is a counting query only take PARTICIPANTS (athletes, I believe) from JOIN, only medals would be counted, whether individual or collective.

  • 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.

1 answer

2


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
  • Yes it reduces the results obtained, but in collective sports it continues to count the medal of each participant, and not just a medal.

  • For example, if a country wins in Olympic football, instead of 11 medals, it should count only 1 medal, as it is a collective event.

  • But in its table RESULTS has only the team’s classification, correct?

  • The RESULTS table only contains individual data. The entire database structure refers to individual data.

  • Well I made a modification in the command, now it receives the data in distinct mode or just a line different from the other but I believe you will have to modify this code to include some things in the select line, if the idevento and the typoevento are equal for several years for example this code will give error then you will have to include something that differentiates the year.

  • I saw where I would take the year and made the modification including the year, but check if the code will return exactly what you need.

  • Well, the query does not work since the columns , PARENT NAME, RESULT that are in select are invalid and PARENT NAME in group by also.

  • I added a query that already does ALMOST what I want, the problem is that it presents to each parent all the classes. Everything needs to be put together.

  • Strange not to work claiming it is invalid, because the internal select generated a new table (in memory) with these fields. So I used AS. (SELECT DISTINCT COUNTRIES.NOMEPAIS AS NOMEPAIS, RESULTADOS.CLASSIFICACAO AS RESULTADO) was put this way? Anyway I made a modification named the new table as A and putting in Select, who knows.

  • It worked! Thank you very much.

Show 5 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.