user1012451 user1012451 - 10 days ago 5
MySQL Question

COUNT query in MYSQL

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:
enter image description here

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

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
Comments