I try to get the most type of a Entrie from a specific Type. To make it clear, i will start with the example. My column type is a foreign key in reality.

**Example:** 

My counter table (~ 1.500.000 rows), it counts all seen cars at different traffic lights:

```
id, datetime, type, traffic_light_id
0, '09:59', 'red ford', 2
1, '10:00', 'black bmw', 1
2, '10:11', 'red ford', 1
3, '10:30', 'yellow ford', 1
4, '11:01', 'black bmw', 1
5, '11:09', 'red ford', 1
6, '11:18', 'green mercedes', 1
7, '11:20', 'black bmw', 2
8, '11:44', 'white renault', 2
9, '11:44', 'red renault', 1

```

i got an index on each column. id is the primary key.

**Needed:** 

So what i like to get is a Result for 'black bmw' with traffic\_light\_id = 1.
The result should sum up all bevore and after the 'black bmw' seen types of cars.

The result should be:

```
count, type, traffic_light_id
2, 'red ford', 1
1, 'yellow ford', 1

```

**My bad Solution:** 

So my solution till now was to iterate trough all counts in counter where type = 'black bmw' and traffic\_light\_id = 1. 

For each count i get the bevore and after seen element with traffic\_light\_id also (that makes two querys in addition for each count) in my php script.

After that i group the result by the given car types in my array and count their frequency.

Is there any better solution for something like that? The best thing would be one sql query only!