CODEWITHSUNDEEP
c#mysql.netsqliis
YosiFZ
YosiFZ

Reputation: 7900

MySql Select all data from column and count

I am selecting with this Command all the rows from column:

SELECT city From Users

With this i am getting all the cities from my table.

Now i want to select all the column data and the count for each one.for example:

New York 20
Los angeles 46
London 35

What should be the command for this?

Upvotes: 5

Views: 2882

Answers (5)

Sathish D
Sathish D

Reputation: 5034

You can use Count for this:

SELECT `city`,
 COUNT(*)
 FROM `users`
GROUP BY `city`;

Upvotes: 0

chetan
chetan

Reputation: 2886

  SELECT City, COUNT(*)
    FROM Users
GROUP BY City

Upvotes: 1

Soner Gönül
Soner Gönül

Reputation: 98750

Since we don't know your column names, you can use a structure like;

From MySQL - Count()

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement.

SELECT city, COUNT(city)
FROM Users
GROUP BY city

Here a SQL Fiddle DEMO.

|        CITY | COUNT |
-----------------------
|      London |    10 |
| Los angeles |    10 |
|    New York |     8 |

Upvotes: 0

Bill Gregg
Bill Gregg

Reputation: 7147

select city, 
       count(*) 
from users 
group by city

Upvotes: 0

Darren
Darren

Reputation: 70728

You can use a GROUP BY

SELECT City, COUNT(Field)
FROM Users
GROUP BY City

Upvotes: 5

Related Questions