create view that displays the pilot roster

I would like to create a view that displays the pilot roster including the current and pending flight experience. The pilots fly drones that deliver groceries. My issue is with the pending deliveries. I joined two tables to get the other columns which are username, license ID, store ID, droneTag, and current experience. I have created a nested query that calls the correct pending flight experience per pilot.

select d.pilot, count(o.orderID) as pending_deliveries from drones as d, orders as o where d.storeID = o.carrier_store and d.droneTag = o.carrier_tag group by d.pilot;

However when I join the query to the rest of the query, it gives me all the pilots pending flight experience on every pilots' pending flight experience, as shown below. It will show that awilson has three rows of pending experience with 2, 1, 1. awilson should only have one of the 1s as their pending experience. I tried adding a group by pilot, but it returns all of the pilots with an output of 2 pending experiences when it should vary based on the nested query. What should I do to attach each pending experience to its specific pilot?

Thanks

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum