Search code examples
mysqlsqlvoting

MySql query to count song votes in music chart

I have 4 tables for a music voting site.

  1. Songs -id.
  2. Charts - id.
  3. Chart_song - id, song_id, chart_id. (join table)
  4. votes - id, song_id_fk, chart_id_fk.

I want to count how many votes each song has for a particular chart (id = 4)

Here is how the vote table looks when people vote for a song (id = 1) to chart id (4)

id    |  song_id_fk  |  chart_id_fk |
 1    |    1         |     4        |
 2    |    1         |     4        |

Hopefully u understand. Please help. If I pass 'WHERE chart_id_fk = 4', I want to get a count of 2 for song_id_fk = 1.

Solution

  • I guess you are looking for something like

    select count(*) as vote_count, song_id_fk, chart_id_fk from votes group by song_id_fk, chart_id_fk