Emulating uniq -c in sql

Given the list ...

A
B
A
A

... with the desired output ...

A 3
B 1

One way to do this on the Unix command line is

cat list | sort | uniq -c

Is there a straightforwar开发者_如何转开发d way to do it in standard SQL?

Use the COUNT aggregate function:

  SELECT t.column,
         COUNT(*)
    FROM YOUR_TABLE t
GROUP BY t.column
ORDER BY t.column

In SQL you can use a group by clause.. e,g:

SELECT COLUMN_NAME, COUNT(1)
  FROM YOUR_TABLE
GROUP BY COLUMN_NAME

http://en.wikipedia.org/wiki/Group_by_(SQL)#Queries

select 
  letter_,
  count(*)
from 
  table_
group by
  letter_
order by 
  count(*)