CODEWITHSUNDEEP
sql
Gregory Sims
Gregory Sims

Reputation: 551

SQL Count occurrences of non-unique column

Suppose I have a SQL table looking something like this:

--------------------
|  id|         name|
--------------------
|   1|        Alice|
|   2|          Bob|
|   3|        Alice|
|   4|        Alice|
|   5|         Jeff|
| ...|          ...|
--------------------

Is it possible to formulate a query which returns a list of names and the number of times they occur? I've made a solution to this by querying all the rows, removing duplicates counting and then ordering; which works, but just looks messy. Can this be neatened up in a SQL query?

Upvotes: 1

Views: 1373

Answers (1)

Psi
Psi

Reputation: 6783

This is standard SQL and should deliver your expected result:

select name, count(*)
from  tblName
group by name
order by name

If you want to order by the count in descending order, you can use:

select name, count(*)
from  tblName
group by name
order by 2 DESC

Upvotes: 2

Related Questions