LEFT OUTER JOIN SUM doubles problem

Question

Table: Shopping

shop_id shop_name  shop_time
1   Brian  40
2   Brian   31
3   Tom   20
4   Brian   30

Table:bananas

banana_id  banana_amount  banana_person
1    1     Brian
2    1     Brian

I now want it to print:

Name: Tom | Time: 20 | Bananas: 0
Name: Brian | Time: 101 | Bananas: 2

I used this code:

$result = dbquery("SELECT tz.*, tt.*,
SUM(shop_time) as shoptime,
count(banana_amount) as bananas

 FROM shopping tt
 LEFT OUTER JOIN bananas tz ON tt.shop_name=tz.banana_person
 GROUP by banana_person
LIMIT 40
");



while ($data5 = dbarray($result)) {

echo 'Name: '.$data5["shop_name"].' | Time: '.$data5["shoptime"].' | Bananas: '.$data5["bananas"].'<br>';


}

The problem is that I get this instead:

Name: Tom | Time: 20 | Bananas: 0
Name: Brian | Time: 202 | Bananas: 6

I just don't know how to get around this.

Answer 1

The problem is that you are constructing a cross product of the two tables which multiplies the results up by the number of rows in the opposite table. To solve this first calculate the result of aggregating one of the tables in a derived table and join this aggregated result to the other table.

SELECT
    shop_name,
    shoptime,
    IFNULL(SUM(banana_amount), 0)
FROM (
    SELECT shop_name, SUM(shop_time) as shoptime
    FROM shopping
    GROUP BY shop_name
) tt
LEFT JOIN bananas tz ON tt.shop_name=tz.banana_person
GROUP BY shop_name

Answer 2

Using * is the issue (since you are using group by). Also, the SUM(shop_time) is being multipled by as many rows in banaanas hence you are getting 202(for two rows in bananas)

Try this query:

SELECT tt.shop_name, 
           SUM(shop_time)           AS shoptime, 
           Ifnull(banana_amount, 0) AS bananas 
    FROM   shop tt 
           LEFT OUTER JOIN (SELECT banana_person, 
                                   SUM(banana_amount) AS banana_amount 
                            FROM   bananas 
                            GROUP  BY banana_person) tz 
             ON tt.shop_name = tz.banana_person 
    GROUP  BY shop_name;