Mysql LEFT JOINing same table twice mysql&gt; SELECT * FROM customers;
+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+

mysql&gt; SELECT * FROM purchases;
+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+


So i have two tables and want to see a column with a customer name then a column with total number of dogs purchased followed by a column for cats and this is what i came up with

mysql&gt; SELECT c.name, sum(dogs.quant) AS dogs, SUM(cats.quant) AS cats
    -&gt; FROM customers AS c
    -&gt; LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog'
    -&gt; LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat'
    -&gt; GROUP BY c.name;
+------+------+------+
| name | dogs | cats |
+------+------+------+
| John | NULL |    5 |
| Matt |    3 |    8 |
+------+------+------+


Can someone explain why Matt has 8 cats when i expect it to be 4? This is a boiled down version of my problem
Perhaps it is easiest to see why this is happening by removing the aggregate functions:

SELECT c.name
     , dogs.quant
     , cats.quant
FROM customers AS c  
LEFT JOIN purchases AS dogs 
    ON c.id=dogs.owner_id 
    AND dogs.type = 'dog'     
LEFT JOIN purchases AS cats 
    ON c.id=cats.owner_id 
    AND cats.type = 'cat'     

+------+-------+-------+
| name | quant | quant |
+------+-------+-------+
| Matt |     1 |     4 |
| Matt |     2 |     4 |
| John |  NULL |     3 |
| John |  NULL |     2 |
+------+-------+-------+


You get a "multiplier effect" since you are joining the purchase table twice. Here's an alternative solution:

SELECT c.name
    , sum(case when type = 'dog' then quant end) AS dogs
    , sum(case when type = 'cat' then quant end) AS cats 
FROM customers AS c  
JOIN purchases p
    ON c.id=p.owner_id    
GROUP BY c.name;