CODEWITHSUNDEEP
sqljoingroup-bycount
AbdeAMNR
AbdeAMNR

Reputation: 145

For vehicles with 2 or more suppliers, COUNT the number of parts (vehicle_part) and suppliers (supplier) per vehicle

data model

- Goal: Count the number of suppliers (supplier) and parts (vehicle_part) for each vehicle for which there are two or more suppliers.

- requirements: Expected columns: vehicle_name, vehicle_part_count, supplier_count in that order

example

Upvotes: 0

Views: 1436

Answers (2)

cheikh tidiane Seck
cheikh tidiane Seck

Reputation: 1

select v.vehicle_name, count(distinct vp.vehicle_part_id) as 
vehicle_part_count, count(distinct vps.supplier_id) as supplier_count
from vehicle_part vp 
join vehicle_part_supplier vps on 
vp.vehicle_part_id=vps.vehicle_part_id
join vehicle v on vp.vehicle_id = v.vehicle_id 
group by v.vehicle_name
having count(distinct vps.supplier_id)>=2;

Upvotes: 0

Rufflito
Rufflito

Reputation: 106

Goal: Count the number of suppliers (supplier) and parts (vehicle_part) for each vehicle for which there are two or more suppliers <-- for each part ?

There could be like 5 parts of a vehicle so 1 supplier per part = 5 different suppliers, does that count or not ?

Upvotes: 1

Related Questions