CODEWITHSUNDEEP
phpmysqlsqldatabase
Average Joe
Average Joe

Reputation: 4601

sql statement to alphabetize and count

Here is the 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 an alphabetized list sorted by count as follows

terms  count
a      4
b      5
c      1

What mySQL statement do I need for that?

Upvotes: 3

Views: 306

Answers (3)

MikeyObviously
MikeyObviously

Reputation: 39

I had a very similar need for a used record store to display artists in stock alphabetically with their count in parenthesis e.g.:

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

Note that I used SELECT DISTINCT when pulling from my table "records". 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 in my site is more complicated, but this is the bare bones of it. Hope that helps...

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176906

Read : GROUP BY (Transact-SQL)

Groups a selected set of rows into a set of summary rows by 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 individual rows.

You just need to apply group by clause for getting result 

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

Upvotes: 6

nickb
nickb

Reputation: 59699

I believe something like this will work:

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

Upvotes: 6

Related Questions