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

Accepted Answer

Your `left join`s 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.

SQL Count from joined table

Solution

Problem