Left join, sum and count group by

Question

I have 3 tables: goods, store and pics. In the first table goods titles are stored. In the second - balance of goods in different stocks, in the third - links to goods pictures. So goods has one-to-many connection with store and pics. Now, I need to get a list of goods with sum of stock rests and count of pictures by one query. I did it like this:

SELECT good.id, good.title, sum(store.rest) AS storerest, count(pics.id) AS picscount 
FROM goods 
LEFT JOIN store ON (goods.id = store.goodid) 
LEFT JOIN pics ON (goods.id = pics.goodid) 
GROUP BY goods.id`

All seems ok while good has 0 or 1 picture. But when it has 2 - storerest doubles, and I can't understand why. What's wrong?

Answer 1

Your issue is that when you have two (or more) store rows and two (or more) pics rows for a single goods row, you end up with the product of all the combinations of rows.

To fix this, do your aggregation before joining:

SELECT 
  good.id, 
  good.title, 
  IFNULL(s.storerest, 0) AS storerest, 
  IFNULL(p.picscount, 0) AS picscount
FROM goods 
LEFT JOIN (
  SELECT goodid, sum(rest) AS storerest
  FROM store
  GROUP BY goodid
) s ON (goods.id = s.goodid) 
LEFT JOIN (
  SELECT goodid, count(id) AS picscount
  FROM pics
  GROUP BY goodid
) p ON (goods.id = p.goodid) 

Answer 2

You are joining together table 'goods' with two other tables, where these two other tables have a one-to-many relations to the 'goods' table. When they are joined, a combination of rows will results - so if there are 2 pics then store items are listed twice.

The easiest way to solve this if you first calculate the stats of the sub-tables and then you join them and use distinct counting when counting unique items, so for example you query should really be:

SELECT good.id, good.title, sum_rest AS storerest, count(distinct pics.id) AS picscount 
FROM goods 
LEFT JOIN (select goodid, sum(rest) as sum_rest from store) s ON (goods.id = s.goodid) 
LEFT JOIN pics ON (goods.id = pics.goodid) 
GROUP BY goods.id