need an sql query

Question

I currently have two tables: 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

Answer 1

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.

Answer 2

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

Answer 3

Tables
1. car(plate_number, brand, cid) 2. 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;