Honesty Rocks! truth rules.

Sql Results how can I arrange my results by recurrance

HOME      >>       Websites and Web Designing

mandla

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

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.

web_designer

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

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