-1

I have a table named "places" that has similar values in one column like in the following data, and I want to find the distinct values, display their count/number of occurrences, sort them in descending order, and limit results to only three values:

name           residents
San Andres     50
San Felipe     143
San Juan       810
San Pablo      352
San Pedro      229
San Vicente    62
Santa Maria    500
San Juan       129
San Andres     88
Santo Rosario  55
San Juan       717
San Vicente    111

I want the result to be:

San Juan       3
San Andres     2
San Vicente    2

I tried the following statement but it gives a different result:

SELECT DISTINCT(name) AS distinctName, COUNT(name) FROM place ORDER BY COUNT(distinctName) DESC LIMIT 3;
JAT86
  • 997
  • 12
  • 24

2 Answers2

1
select name, count(*) from place group by (name) order by count(*) desc
theunknownSAI
  • 300
  • 1
  • 4
0

To accumulate results over different rows you need to use GROUP BY. GROUP BY returns a single result row per unique combination of the GROUP BY fields.

This tutorial provides some concerte examples of how GROUP BY works https://www.w3schools.com/sql/sql_groupby.asp

You may also want to check this previous related question How does GROUP BY work?

Kim Mens
  • 325
  • 1
  • 13