58

I have a table which stores IDs and the city where the store is located.

I want to list all the stores starting with the stores that are in the city where there are the most stores.

TABLE

ID CITY
1  NYC
2  BOS
3  BOS
4  NYC
5  NYC

The output I want is the following since I have the most stores in NYC, I want all the NYC location to be listed first.

1  NYC
4  NYC
5  NYC
2  BOS
3  BOS
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
Enkay
  • 1,898
  • 6
  • 24
  • 35
  • Exact Answer https://stackoverflow.com/questions/26187033/mysql-order-by-number-of-occurrences-of-an-element-in-a-column-of-mysql-table?noredirect=1&lq=1 – A. Sang Jan 11 '18 at 16:14

4 Answers4

77
SELECT count(City), City
FROM table
GROUP BY City
ORDER BY count(City);

OR

SELECT count(City) as count, City
FROM table
GROUP BY City
ORDER BY count;

Ahh, sorry, I was misinterpreting your question. I believe Peter Langs answer was the correct one.

MindStalker
  • 14,629
  • 3
  • 26
  • 19
  • Maybe I'm not doing it right but when I use "group by" instead of listing each individual store it will only list 1 store for NYC and 1 store for BOS. Any idea what could be going wrong? – Enkay Feb 17 '10 at 18:25
  • 1
    Enkay: That's exactly what group by does. It condenses identical entries. – EmFi Feb 17 '10 at 18:26
  • 2
    Yea but my stores are not identical, I have 3 different stores in NYC with a unique store ID. I need to list each individual store. I just want them ordered by the city with the highest number of stores. – Enkay Feb 17 '10 at 18:27
  • 3
    If you add Id to the 'GROUP BY' it will list all stroes – giladbu Feb 17 '10 at 18:29
34

This one calculates the count in a separate query, joins it and orders by that count (SQL-Fiddle):

SELECT c.id, c.city
FROM cities c
JOIN ( SELECT city, COUNT(*) AS cnt
       FROM cities
       GROUP BY city
     ) c2 ON ( c2.city = c.city )
ORDER BY c2.cnt DESC;
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
  • how would you be able to add WHERE to this? – jdwee Jun 04 '18 at 20:53
  • 2
    @jdwee: You would add it before the `ORDER BY` clause. If it should also reflect on the COUNT, then you would either have to repeat it within the subquery, or better: use a [CTE](https://dev.mysql.com/doc/refman/8.0/en/with.html). If this does not help, please post a separate question instead. – Peter Lang Jun 05 '18 at 07:37
6

This solution is not a very optimal one so if your table is very large it will take some time to execute but it does what you are asking.

 select c.city, c.id, 
      (select count(*) as cnt from city c2 
       where c2.city = c.city) as order_col
 from city c
 order by order_col desc

That is, for each city that you come across you are counting the number of times that that city occurs in the database.

Disclaimer: This gives what you are asking for but I would not recommend it for production environments where the number of rows will grow too large.

Vincent Ramdhanie
  • 102,349
  • 23
  • 137
  • 192
2
SELECT `FirstAddressLine4`, count(*) AS `Count` 
FROM `leads` 
WHERE `Status`='Yes'
AND `broker_id`='0'
GROUPBY `FirstAddressLine4` 
ORDERBY `Count` DESC 
LIMIT 0, 8
David
  • 208,112
  • 36
  • 198
  • 279
Corrie
  • 21
  • 1