Geek asks and answers

Sql alphabetical expression and score

Here is mySQL I got

id   terms    
1    a       
2    c       
3    a       
4    b       
5    b       
6    a       
7    a
8    b
9    b
10   b

I want to get a list in alphabetical order, sorted by count, as follows

terms  count
a      4
b      5
c      1

What do I need for a mySQL statement?

+3
source share
3 answers

I believe something like this will work:

SELECT terms, COUNT( id) AS count 
    FROM table 
GROUP BY terms 
ORDER BY terms DESC
+6
source

Read: GROUP BY (Transact-SQL)

Groups the selected rowset into a set of summary rows with the values ​​of one or more columns or expressions in SQL. One row is returned for each group. Aggregate functions in the SELECT clause list provide information about each group instead of single lines.

You just need to apply the offer group byto get the result.

select terms, count (id) as count from table 
group by terms 
order by terms
+6

, , :

Smokey Robinson and Miracles (2) | Sonic Youth (2) | Spoon (3) | Steely Dan (1) | Stevie Wonder (2) | Sufyan Stevens (1) |

Note that I used SELECT DISTINCT when I pulled “records” from the table. Here are the relevant code snippets:

//QUERY
$arttool = mysql_query("SELECT DISTINCT * FROM records GROUP BY artist ORDER BY artist ASC");

//OUTPUT LOOP START
while($row = mysql_fetch_array($arttool)){

//CAPTURE ARTIST IN CURRENT LOOP POSITION
$current=$row['Artist'];    

//CAPTURING THE NUMBER OF ALBUMS IN STOCK BY CURRENT ARTIST
$artcount = mysql_num_rows(mysql_query("SELECT * FROM records WHERE artist = '$current'"));

//ECHO OUT. 
echo $current . "($artcount)";

The actual code on my site is more complex, but it's its bones. Hope this helps ...

+1
source

More articles:


All Articles