SQL Count from joined table

Question

I have a table 'lijsten', a table 'werknemerlijsten' and a table 'categorieen'.

Now i'm using the Query to get the count

SELECT id, naam, beschrijving, count(wl.werknemer_id) as aantal
FROM lijsten l
LEFT JOIN werknemerlijsten wl
ON l.id = wl.lijst_id
GROUP BY l.naam
ORDER BY naam

But when i try the same query with another count, from another table, the results are false.

SELECT l.id, l.naam, beschrijving, count(c.lijst_id) as aantal_cat, count(wl.lijst_id)    as aantal_lijst
FROM lijsten l
LEFT JOIN werknemerlijsten wl ON l.id = wl.lijst_id
LEFT JOIN categorieen c ON l.id = c.lijst_id
GROUP BY l.naam
ORDER BY naam

Any idea what i might be doing wrong? Thanks

Solution

Your left joins are bringing in tables that have multiple matches for a given id. The quick and easy way to fix counts is to use count(distinct) instead of count():

SELECT l.id, l.naam, beschrijving,
       count(distinct c.lijst_id) as aantal_cat, count(distinct wl.lijst_id) as aantal_lijst
FROM lijsten l
LEFT JOIN werknemerlijsten wl ON l.id = wl.lijst_id
LEFT JOIN categorieen c ON l.id = c.lijst_id
GROUP BY l.naam
ORDER BY naam;

An alternative approach is to aggregate the tables before the join, doing the counts in the subquery.

Comments

[Raymond Nijland]

distinct will look for not null unique values, and try not using Dutch in your table names if you program. i know its hard when you are dutch. i can understand the table names but not the english poeple on this site..

[Miguel Stevens]

Thank you! And i'll remember the not-dutch thing. I was thinking of translating it but do the table names really matter? :)

[Raymond Nijland]

you didn't give a structure if the relation is unclear then yes table names do matter and it doenst read nice if you read english and dutch mixed up in a SQL statement..

[Blazemonger]

Don't forget to accept the original answer by GordonLinoff if it was helpful. Doing so encourages others to help you with future questions.