Dynamic mapping with SQL

user_1151724 0
Viewed 35

I have an SQL db which contains a table of license plate numbers (plates), a table of parking lots (places), and a table corresponding one to the other over time (parking), each row placing a specific vehicle plate in a specific place at a particular time.

create table parking (
    plateid integer,
    placeid integer,
    time_period integer
);

This means each row as a whole is unique but the plate/place combinations are not. I need to count how many times each plate appears in each place. There are an indeterminate number of both, so I cannot maintain a table per place and simply count the entries.

This is easy enough using a general purpose programming language applied to the list. Is there a way to do it with straight SQL?

dictionary sql
2 Answers

Are you just looking for aggregation?

select plate_id, place_id, count(*)
from parking
group by plate_id, place_id;
user_11440350

You can group by both plate and place and count occurrences:

SELECT place_id, place_id, count(*)
FROM parking
GROUP BY place_id, plate_id
user_109104800
Related