Oracle SQL Query Question

[PampaZiya]

I've been scratching my head for a long time but it's not working the way I want it..

 

The question is: 

Find by type of vehicles the number of interventions done this year.

 

 

My query is this:

 

select  v.vtype as VEHICLE_TYPE, count (intervno) as NUMBER_INTERVENTIONSfrom intervention i, vehicle vwhere i.matno = v.immatno and extract(year from intervdate) = extract(year from sysdate)group by vtype

but this will only return the tuples which actually have interventions, so let's say a vehicle for type f124 will not appear, instead of appearing f124 and the count = 0

 

I tried using union, this way:

select  v.vtype as VEHICLE_TYPE, count (intervno) as NUMBER_INTERVENTIONSfrom intervention i, vehicle vwhere i.matno = v.immatno and extract(year from intervdate) = extract(year from sysdate)group by vtypeUNIONselect distinct v.vtype, 0from vehicle vwhere NOT EXISTS ( select * from intervention i where i.matno = v.immatno);

But this will return duplicates, so let's say f123 has 1 intervention.. it will show f123 - 1 and then f123 - 0...

Any help would be appreciated, I don't need the code, just a way of thinking.. I've done harder queries but this is just not clicking in my head.

 

[Mr_KoKa]

Is intervention row connected with vehicle row by matno -> immatno?

Edit:

I don't see you following this thread but anyway look at this:

http://sqlfiddle.com/#!9/a1319/13/0

I use left join so there will be always a row for a vehicle even if there is no interventions to connect to it.