CODEWITHSUNDEEP
mysqlsqldatabase
Waseem Senjer
Waseem Senjer

Reputation: 1048

How do I join two tables to get statistics

I have a MySQL database with two tables in it:

Members
 - id, name, city_id

and:

Cities
- id, name

I want a query that will return each city name and how many members are in it.

The output I need is:

city_name    count
------------------
New York     15
Los Angeles  20

Upvotes: 0

Views: 142

Answers (5)

Joe
Joe

Reputation: 15802

  SELECT
         c.name AS city_name,
         COUNT(m.id) AS counter
    FROM Members m
    JOIN Cities c ON m.city_id = c.id

Upvotes: 1

blejzz
blejzz

Reputation: 3349

something like this:

SELECT a.name, COUNT(DISTINCT b.city_id) 
FROM Cities a JOIN Members b 
ON b.city_id = a.id  
GROUP BY b.city_id

Upvotes: 1

Allen
Allen

Reputation: 1

Try something like this:

select city.name, count(member.id)
from city
inner join members
on city.id = members.city_id
group by city.name

Hope this helps.

Upvotes: 0

Marco
Marco

Reputation: 57593

Try this

SELECT c.name city_name, COUNT(m.id) counter
FROM Cities c INNER JOIN Members m
ON c.id = m.city_id
GROUP BY c.name

Upvotes: 0

Jan S
Jan S

Reputation: 1837

select 
    cities.name as city_name, 
    count(cities.id) as counter
from 
    members inner join cities 
on 
    members.city_id = cities.city.id

Upvotes: 0

Related Questions