I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding columns to upper select.

select j.name, m.rescount
    (select count(*) as rescount
    from depot_resource as m
    where j.scheduled_time between m.actual_start and m.actual_end)
from transit as j

You can't reference depot_resource in the outer query, it is out of scope. You need to join the tables, and group by name to get the aggregate. Since you didn't provide DDL and sample data, we can only guess, but maybe you need something like this?

SELECT j.name, COUNT(*) AS rescount
FROM transit as j 
     depot_resource as m 
     on j.scheduled_time between m.actual_start and m.actual_end
GROUP BY j.name

Something seems odd here as there is no correlation to j.name in depot_resource, but without the table structure and the expected result we can't really tell.


| improve this answer | |
  • Thank you for your fast response. Transit and depot_resource are separate tables without constraints. There are number of resources (rows) working on schedule. When Transit arrives depot, I need to show Transit name and number of available resources on that time. I got your answer working with my tables. – Jomppa Dec 5 '18 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.