CODEWITHSUNDEEP
mysqlsql
david strachan
david strachan

Reputation: 7228

COUNT() in SELECT not producing desired output

I have 2 tables:

usStateCities:
usStateCities

And usstatescentroids:
usstatescentroids

SELECT state,COUNT(*) AS  count  FROM usStateCities  GROUP BY state

Gives the number of cities in each state

SELECT *
FROM (SELECT state, COUNT(*)  AS count FROM  usstatecities ) AS a,
usstatescentroids b
WHERE a.state = b.state
GROUP BY state

Only gives one state and total number of cities in US I want to find the number of cities in each state and the coordinates (lon,lat) of the centroid of each state.

CORRECTION I have change State to state. I cut and pasted older statements.

Upvotes: 3

Views: 161

Answers (2)

Kermit
Kermit

Reputation: 34063

SELECT a.state, a.count, b.lon, b.lat 
FROM (SELECT State, COUNT(*) AS count FROM usStateCities GROUP BY State) a
LEFT JOIN usstatescentroids b ON b.state = a.State

Upvotes: 4

Habibillah
Habibillah

Reputation: 28695

I prefer to use join table then subquery. try bellow:

SELECT b.State, b.lon, b.lat, COUNT(*) AS  count  
FROM usStateCities  a, usstatescentroids b
WHERE a.state = b.state
GROUP BY b.State, b.lon, b.lat

Upvotes: 2

Related Questions