Task 9 (aero)

–Among the clients which only use a single company, find the different passengers who have flown more often than others.

–Result set: passenger name, number of trips, and company name.

select p.name, cnt, c.name from

(

select pt.ID_psg, t.ID_comp, COUNT(ID_comp) cnt from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no

where pt.ID_psg in

(select ID_psg from

(select distinct pt.ID_psg, t.ID_comp from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no) x

group by ID_psg

having COUNT(ID_comp) = 1)

group by pt.ID_psg, t.ID_comp) x

inner

join Company c on c.ID_comp = x.ID_comp

inner

join Passenger p on p.ID_psg = x.ID_psg

where

cnt =

(

select max(cnt) from

(select pt.ID_psg, t.ID_comp, COUNT(ID_comp) cnt from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no

where pt.ID_psg in

(select ID_psg from

(select distinct pt.ID_psg, t.ID_comp from Trip t

inner join Pass_in_trip pt on pt.trip_no = t.trip_no) x

group by ID_psg

having COUNT(ID_comp) = 1)

group by pt.ID_psg, t.ID_comp) x )

 
from forum:
 

select

p.name, cnt, c.name

from

(

select

top 1 with ties pt.id_psg, count(*) as cnt, min( id_comp ) as id_comp

from pass_in_trip pt

inner join trip t on t.trip_no = pt.trip_no

group by pt.id_psg

having count( distinct id_comp ) = 1

order by count(*) desc

)

t

inner

join passenger p on t.id_psg = p.ID_psg

inner

join Company c on c.ID_comp = t.ID_comp

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s