CODEWITHSUNDEEP
sqloracle-database
AIP
AIP

Reputation: 3

Oracle - Not getting the desired result

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

Answers (1)

Aleksej
Aleksej

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

Related Questions