Thanks for the help so far guys!!! I am really new to MySQL and database. But I am trying my best.
So I have these relations:
station (station_id, latitude, longitude)
station_control_manager (station_id, ctrl_unit_id)
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?