Sql Results how can I arrange my results by recurrance

[mandla 0]

I am trying make a voting system for my site and I have a part where people can vote certain things say in this case Top Hits

In my Database I have a Table called Tophits, which has the following feilds. Song Name, Artist and CloseDate

for this question I will provide a rough draft of my entries in databse.

TABLE =  TOPHITS------------------------------------------------------------------------------------------------Song                                 Artist                                 CloseDate------------------------------------------------------------------------------------------------Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-06Hello                                Chorus                                 2010-08-20Hello                                Chorus                                 2010-08-20Hello                                Chorus                                 2010-08-20Hello                                Chorus                                 2010-08-20Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Hello                                Chorus                                 2010-08-13Hello                                Chorus                                 2010-08-13Hello                                Chorus                                 2010-08-13Hello                                Chorus                                 2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13

What I am looking to have as a result a count of only songs with a close date = 2010-08-13 arranged by the number of occurances.
but instead of repeated results like

Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Goodbye                              Hippies                                2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Whats Up                             Singer                                 2010-08-13Hello                                Chorus                                 2010-08-13Hello                                Chorus                                 2010-08-13Hello                                Chorus                                 2010-08-13Hello                                Chorus                                 2010-08-13

I would instead like results that would only display a count and automatically should rearrange the list into descending order witht he most recurring song at the top and the least recurring one at the bottom. Similar to the one listed below.

Goodbye                              Hippies                 12            Whats Up                             Singer                   6Hello                                Chorus                   4

Thanks for all the help.

[Quatrux 4]

I think it would be something like this:

SELECT song,artist,count(song) as hits FROM table1 GROUP BY song, artist;

Furthermore, I presume you would need to order by by hits descending or ascending, the way you want.

But what if the song is with the same name, but the artist is different? When I would suggest to do concat song and artist into one value to make it unique.
Edited August 26, 2010 by Quatrux (see edit history)

[web_designer 7]

your script should be like this

<?php$dbhost = 'your server name';$dbuser = 'your user name';$dbpass = 'your password';$con = mysql_connect($dbhost, $dbuser, $dbpass) or die  ("couldn't connect to database");$db= mysql_select_db ("your database name", $con);if ($query1= mysql_query ( " SELECT * FROM tophits WHERE closedate= '2010-08-13' AND song= 'Goodbye'")) {$row1= mysql_fetch_array ($query1);$num1= mysql_num_rows ($query1);echo "$row1[song]"," ","$row1[artist]"," ",$num1, " ","<br/>";}if ($query2= mysql_query ( " SELECT * FROM tophits WHERE closedate= '2010-08-13' and song= 'Whats Up'")) {$row2= mysql_fetch_array ($query2);$num2= mysql_num_rows ($query2);echo "$row2[song]"," ","$row2[artist]"," ",$num2, "<br/>";}if ($query3= mysql_query ( " SELECT * FROM tophits WHERE closedate= '2010-08-13' and song= 'Hello'")) {$row3= mysql_fetch_array ($query3);$num3= mysql_num_rows ($query3);echo "$row3[song]"," ","$row3[artist]"," ",$num3, "<br/>";}mysql_close ($con);?>

i test it and it worked perfectly, so if you have any questions feel free to ask me. good luck.

[SzamanGN 0]

That solution will solve your problems:

SELECT `song`,`artist`,COUNT(`id_th`) AS `countedvotes` FROM `tophits` WHERE `closedate`="2011-01-03" GROUP BY `song`, `artist` ORDER BY `countedvotes` DESC

It will show counted votes for the songs and artists.
Even if you have more then one the same name for the song or artist, you will have the correct statistics (I have tested it).