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