I have this query generated by
Django where a user has
jobs as related tables.
jobs are of two types:
wash. I want to count for each user shown the number of orders, clean jobs and wash jobs.
One of the users has 3 clean jobs in the database but this query returns the value of 6 for column
clean_ct. If I remove the join to the
order table and the orders count the counts are as expected so the second
LEFT OUTER JOIN is causing this doubling to occur. I'm suspecting a wrong
GROUP BY but I haven't found a solution. Any ideas?
SELECT DISTINCT `auth_user`.`id`, `auth_user`.`username`, Count(DISTINCT `order`.`id`) AS `orders_ct`, Count(CASE WHEN `job`.`service` = 'clean' THEN 1 ELSE NULL end) AS `clean_ct`, Count(CASE WHEN `job`.`service` = 'wash' THEN 1 ELSE NULL end) AS `wash_ct` FROM `auth_user` LEFT OUTER JOIN `order` ON ( `auth_user`.`id` = `order`.`user_id` ) LEFT OUTER JOIN `job` ON ( `auth_user`.`id` = `job`.`user_id` ) GROUP BY `auth_user`.`id` ORDER BY `auth_user`.`id` DESC LIMIT 100
clean_ct should be 3 but returns 6