need an sql query

I currently have two tabl开发者_运维知识库es: 1. car(plate_number, brand, cid) 2. borrow(StartDate, endDate, brand, id)

I want to write a query to get all available brand and count of available cars for each brand

halfpseudo count rows or use sql count or group by, left join also applicable where nullvalues

brand from car join borrow on borrow.brand=car.brand where endDate<currentdate

SELECT c.Brand COUNT(plate_number) FROM car as c LEFT JOIN (borrow as b) on c.cid = b.id WHERE endDate < NOW() GROUP BY c.Brand

I did not try it but it should work.

Edit: fixed where.

Tables :

car(plate_number, brand, cid)

borrow(StartDate, endDate, brand, id)

select c.plate_number, b.brand, count(b.brand) as available_number
from   car c, borrow b 
where  c.brand=b.brand
       and end_date>=sysdate 
having count(1)>0
group by c.plate_number, b.brand