Emile Arab Emile Arab - 9 months ago 52
SQL Question

SQL joining two tables and counting

I want to retrieve all the customers who own more than 1 car.

I have this code:

SELECT c.fname,
Count(v.cid) AS nmbrofvehicle
FROM customer c
LEFT JOIN vehicle v
ON c.cid = v.cid
GROUP BY c.fname;

But it returns this error:

ORA-00979: not a GROUP BY expression

select c.fname, c.lname, c.cid, count(v.cid) as nmbrofvehicle
from customer c left join vehicle v on c.cid = v.cid 
group by c.fname, c.lname, c.cid
having count(*) > 1;