Monday, May 21, 2012

COUNT query in MYSQL

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:
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?

No comments:

Post a Comment