help with count with left join

Question

hello

I have 2 tables

• 1st table called services has id_service, name, date.
• 2nd table called services_images has id_img, img_name, id_service.

What I wanna do is get list of services with a row called num_images that counts how many images there are per service.

I tried this

SELECT COUNT(services_images.id_img) as num_images, services.* FROM services
LEFT JOIN services_images ON (services_images.id_service = services.id_service) ORDER BY service.id_service

It doesnt work and I dont know how to fix it. This will return just ONE service (the first id_service: 1) with as num_images the count of all the rows in services_images! Even if change "services_images.id_service = services.id_service" to "services_images.id_service = 2" it stills shows the first ID! So this rules is not even considered.

Thanks

Answer 1

Adding a GROUP BY clause should do the trick

SELECT COUNT(services_images.id_img) as num_images, services.* 
FROM services
LEFT JOIN services_images ON (services_images.id_service = services.id_service) 
GROUP BY service.id_service
ORDER BY service.id_service

This will group your rows by service and the count aggregation will give you the count of images per service.