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

Answer 1

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.