MySQL occurrence calculation with multiple values

Vote count:

0

I have the following busroutes table and need to find the bus stop (on_street + cross_street) that appears on the most routes. My thought process is to create a new table with two columns: bus_stop and route_appearances, where bus_stop is the concatenation of the aforementioned streets and route_appearances is the count of a stop appearing in unique routes. For example, if stop A appears in routes 86,91 and 81W then that would equal 3 for route_appearances. Please note that duplicates should not be counted.

I'm confused on how to calculate the frequency of a specific stop appearing in a route because in the routes table there are multiple values in an entry, separated by a comma. Any suggestions would be very much appreciated

+---------+-----------+--------------------+--------+-----------+------------+-----------------+---------+-----------------------------+
| stop_id | on_street | cross_street       | routes | boardings | alightings | month_beginning | daytype | location                    |
+---------+-----------+--------------------+--------+-----------+------------+-----------------+---------+-----------------------------+
|    9692 | GUNNISON  | MELVINA (west leg) | 81W    | 2.4       | 8.4        | 10/01/2012      | Weekday | (41.96853247, -87.78306761) |
|    9693 | GUNNISON  | MOBILE             | 81W    | 1.9       | 7.7        | 10/01/2012      | Weekday | (41.96849308, -87.78544981) |
|    9694 | GUNNISON  | NAGLE              | 81W    | 37.4      | 42.4       | 10/01/2012      | Weekday | (41.96847297, -87.78781359) |
|    9695 | NAGLE     | STRONG             | 86,91  | 2.9       | 4.4        | 10/01/2012      | Weekday | (41.97012700, -87.78789600) |
|    9697 | NAGLE     | CARMEN             | 86,91  | 0.2       | 2.8        | 10/01/2012      | Weekday | (41.97374900, -87.78782600) |
|    9698 | NAGLE     | FOSTER             | 86,91  | 1.5       | 15.2       | 10/01/2012      | Weekday | (41.97556813, -87.78779559) |
|    9700 | NAGLE     | BERWYN             | 86,91  | 0.1       | 0.7        | 10/01/2012      | Weekday | (41.97739122, -87.78776172) |
|    9701 | NAGLE     | BALMORAL           | 86,91  | 0.8       | 7.3        | 10/01/2012      | Weekday | (41.97918471, -87.78772953) |
|    9702 | NAGLE     | CATALPA            | 86,91  | 1         | 4.5        | 10/01/2012      | Weekday | (41.98100361, -87.78769164) |
|    9703 | NAGLE     | BRYN MAWR          | 86,91  | 2.6       | 148.4      | 10/01/2012      | Weekday | (41.98280027, -87.78762514) |
+---------+-----------+--------------------+--------+-----------+------------+-----------------+---------+-----------------------------+

asked 17 secs ago

Anconia

441

MySQL occurrence calculation with multiple values