Reputation: 3
I can't get this to work, I'm sorry if it seems too easy, but I'm really looking forward for suggestions:
I have 3 tables:
ACCIDENTS
(
ACCIDENT_DATE DATE,
DRIVER_CODE VARCHAR2,
DAMAGES NUMBER,
LOCATION_ACCIDENT VARCHAR2,
CAR_CODE VARCHAR2
)
CARS
(
CAR_CODE VARCHAR2,
MODEL VARCHAR2,
YEAR VARCHAR2,
CODE_OWNER VARCHAR2)
// I didn't use this table ,but it was stated so I didn't want to miss anything
PERSON
(
CODE VARCHAR2,
NAME VARCHAR2,
RESIDENCE VARCHAR2
)
The query is:
display the name and the code of the persons which had more than two accidents in the last 4 months
I have written:
select a.driver_code,a.accident_date,p.name
from accidents a join p person
on (a.driver_code=p.code)
group by a.accident_date,a.driver_code,p.code
having count (a.accident_date)>2
It displays 1 out of 2 drivers that had over 2 accidents, but after I fiddled with my table data it said it could find any data at all.
I know I didn't resolve it for the last 4 months part , because I'm not sure how to use it . This is my idea , although I know this will display (or suppose to anyway ) the months between the system's date and the date of the accident (all of them) whereas I was suppose to display the drivers in the last 4 months:
select a.driver_code,a.accident_date,p.name,
sysdate,accident_date,
months_between(sysdate,accident_date)
from accidents a join p person
on (a.driver_code=p.code)
group by a.accident_date,a.driver_code,p.code
having count (a.accident_date)>2
Upvotes: 0
Views: 47
Reputation: 22979
You may need something like this:
select a.driver_code, p.name
from accidents a
inner join p person
on (a.driver_code=p.code)
where accident_date >= add_months(sysdate, -4)
group by a.driver_code, p.name
having count (1)>2
This way you only consider the accidents within the last 4 months, then count the accidents per user and add another condition to only retrieve the users with at least 2 accidents.
Upvotes: 2