COUNT query in MYSQL

Question

I have these relations:

station (station_id, latitude, longitude)
station_control_manager (station_id, ctrl_unit_id)

Problem

Tabulate station names, ids and total numbers of control areas in the middle town area, e.g., latitude between 40.750 and 40.760 and longitude between -74.000 and -73.95.

This is my query

SELECT DISTINCT s.station_name, s.station_id, count 
FROM station s, 
   (SELECT cm.station_id, COUNT(cm.station_id) as count
    FROM stationcontrolmanager cm GROUP BY cm.station_id) sub_query
WHERE s.latitude >= 40.750 
  AND s.latitude <=40.760 
  AND s.longitude >= -74.000 
  AND s.longitude <= -73.95 
GROUP BY s.station_id;

I count by grouping station_id and count how many times it repeats (by repating, we know how many control area it has).

Instead, I have this:

The count was supposed to be the number of ctrl_unit_id, which in this case, is also the number of station_id. But in DB I count 7 rows, not 2.

The station names are all correct, of course.

Is there something wrong with my SQL statements?

Answer 1

You can do this without sub-queries:

SELECT station.station_id, station_name, COUNT(ctrl_unit_id)
FROM station
LEFT JOIN station_control_manager ON station.station_id = station_control_manager.station_id
WHERE (latitude BETWEEN 40.750 AND 40.760) AND (longitude BETWEEN -74.000 AND -73.95)
GROUP BY station.station_id, station_name

1. Since you need information from both tables, you need to join them using station_id
2. You need to use LEFT JOIN since a station may have zero control managers (RIGHT JOIN will eliminate station records where there is no corresponding control manager record)
3. You need to COUNT(ctrl_unit_id) instead of COUNT(*) since the latter will (still) return 1 when a station has no control unit associated with it.
4. The WHERE clause is self-explanatory

Answer 2

Try this:

SELECT a.station_ID, a.station_name, COUNT(b.station_ID)
FROM station a INNER JOIN station_control_manager b
        on a.station_ID = b.station_ID
WHERE  (a.latitude between 40.75 and 40.76) AND
       (a.longitude between -74.0 and -73.95)
GROUP BY a.station_ID

By grouping the records, it is now OK to omit DISTINCT keyword.