title: Advice needed for sql query. Need to count the amount of time a song has been played in a festival and how many times it won during the finals
body: <p>hope you can help me, this is the structure of the table:
  codIscr (identifies song, who plays it and the event in which it has been played) (Primary Key                        with dataser)
  dataser (the date of the event in which it was played)
  vote
  Posizione(the position relative to the date, the winner of the last date of the event is the final winner)</p>
  
  <p>This is the query i used: </p>
  
  <pre><code>SELECT X.codIscr, count(X.codIscr=Y.codIscr),count(X.Posizione=1 AND Y.Posizione=1) 
      FROM concorre X, concorre Y 
       WHERE X.codIscr=Y.codIscr AND X.dataSer!=Y.dataSer AND 
              X.codIscr IN(SELECT DISTINCT codIscr FROM iscrizione WHERE codEv=1) 
       GROUP BY X.codIscr HAVING count(X.codIscr=Y.codIscr)&gt;1
  </code></pre>
  
  <p>It should give me as result 4 different codIscr, 2 as the amount of time the songs were played in the event (for all 4 of them) and 1 as the amount of times they got first in the event (during "semifinals" and such) exept for the winner of the final date of the event which should have 2 as number of times it got first... Instead all 4 songs as last count give me 2... Can anyone help, plz? </p>