CODEWITHSUNDEEP
sqlcountmany-to-many
Gabriel Matusevich
Gabriel Matusevich

Reputation: 3855

SQL Many to Many Ranking

I have 2 entities related with a many to many relationship

clients, services, clients_has_services.

I need to retrieve all services with their respective count of clients like so:

Service1 20
Service2 10
Service3 5
Service4 0
....

I'm having trouble making that query, this one only return one record:

SELECT
    services.id as id,
    services.name as name,
    COUNT(clients_has_services.services_id) as ranking
FROM services
LEFT JOIN clients_has_services ON services.id = clients_has_services.services_id

Upvotes: 0

Views: 43

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

you need a group by clause:

SELECT
    services.id as id,
    services.name as name,
    COUNT(clients_has_services.services_id) as ranking
FROM services
LEFT JOIN clients_has_services ON services.id = clients_has_services.services_id
GROUP BY
    services.id,
    services.name

Upvotes: 1

Related Questions