Group by using left join and sum() = duplicate aggregates
I have a query here involving LEFT JOIN which works fine but I need to add cats c (categories) and subcats s (subcategories).
I'll post the background to the original problem here:
Fixes has p_id col which corresponds to problems pk (I've grouped these).
PHP Code:
Problem p Fixes f
p_id p_id f_id fix_count
1 1 1 40
2 1 2 20
3 1 3 40
So I wanted to group by fixes.p_id and sum the fix_count to get
PHP Code:
Query
p.p_id p.name sum(fix_count)
1 Name info... 65
There is not always a link to fixes. So I need a LEFT join.
Working a treat now but no cats or subcats:
PHP Code:
SELECT IF(sum(f.num_fixed) IS NULL,0,sum(f.num_fixed)) AS num_fixed, p.*
FROM problem p
LEFT JOIN fixes f ON p.p_id = f.p_id
GROUP BY f.p_id
ORDER BY p.date_added DESC;
Results:
PHP Code:
num_fixed p_id name cat_id
100 47 thing 1
0 48 rktjhrkjh 1
When I add the new joins I have this:
PHP Code:
SELECT IF(sum(f.num_fixed) IS NULL,0,sum(f.num_fixed)) AS num_fixed, p.*
FROM problem p
LEFT JOIN fixes f ON (p.p_id = f.p_id)
JOIN cats c ON c.cat_id=p.cat_id
JOIN subcats s ON s.cat_id = c.cat_id
GROUP BY s.cat_id,f.p_id
ORDER BY p.date_added DESC;
PHP Code:
num_fixed p_id name cat_id
300 47 thing 1
0 48 rktjhrkjh 1
There is one category (hardware) and 3 subcats linked so it groups the three rows and ends up summing three copies of num_fixed. I end up with 300 instead of 100. So I need to know how to group the cols and end up with 100 again with the new joins.
(This was stiched together from 2 examples so some values may not add up right.)
Re: Group by using left join and sum() = duplicate aggregates
