title: Left Joins and Aggregate functions using count and if
body: <p>I have written a query which joins three tables to a customer table, counts values of various fields, and returns an overview of sales and revenue broken down by sales rep:</p>
  
  <pre><code>SELECT 
      u.id AS `userId`, 
      u.`username`, 
      (SELECT COUNT(*) FROM `SaleSet` s WHERE s.`pitchedBy_id` = `userId` AND s.setCompleteAt BETWEEN '2012-07-02 00:00:00' AND '2012-07-02 23:59:59') AS `transfers`,
      COUNT(c.id) AS `closes`, 
      COUNT(IF(c.saleType_id = 1, 1, NULL)) AS `regS_sales`,
      COUNT(IF(c.saleType_id = 2, 1, NULL)) AS `pd_sales`,
      COUNT(IF(c.saleType_id = 4, 1, NULL)) AS `attempted_sales`,
      COUNT(IF(c.CustomerStatus_id IN (5,6,9,16), 1,NULL)) AS `complete`,
      COUNT(IF(c.CustomerStatus_id IN (8,18), 1,NULL)) AS `canceled`,
      COUNT(IF(c.CustomerStatus_id IN (1,12,13), 1,NULL)) AS `pending`,
      COUNT(IF(c.CustomerStatus_id = 20, 1,NULL)) AS `post_dated`,
      SUM(IF(p.saleType_id = 2, p.`authOnlyAmount`,0)) AS `pdPotRev`,
      #SUM(IF(c.saleType_id = 2 AND t.`captured` = 0, p.`authOnlyAmount`,0)) AS `pdCapRev`,
      SUM(t.amount) AS `fwRevAuthed`,
      SUM(IF(p.saleType_id = 2 AND t.`captured` = 0, t.amount,0)) AS `fwCaptured`
  FROM customer c
  LEFT JOIN `User` u ON u.id = c.`salesRep_id`
  LEFT JOIN `Transaction` t 
      ON t.`customer_id` = c.`id`
      AND t.transactionType = 'Auth'
  LEFT JOIN `Purchase` p ON p.`customer_id` = c.`id`
  #WHERE c.`salesRep_id` = 10
  WHERE c.`activationDate` BETWEEN '2012-07-02 00:00:00' AND '2012-07-02 23:59:59'
  GROUP BY u.`id`
  </code></pre>
  
  <p>Why is this column returning a 0 rather than the sum of t.amount: <code>SUM(IF(p.saleType_id = 2 AND t.captured = 0, t.amount,0)) AS fwCaptured</code>?  As you can see, I do the exact same thing two lines above and it works fine.  </p>
  
  <p>Here is an example of the results:</p>
  
  <pre><code>userId  username          transfers  closes  regS_sales  pd_sales  attempted_sales  complete  canceled  pending  post_dated  pdPotRev  fwRevAuthed  fwCaptured  
  10  doughaase                17       4           3         1                0         4         0        0           0     50.00       298.00          0.00
  65  davidgarber              13       5           5         0                0         4         0        0           0      0.00       595.00          0.00
  70  morgantaylor              5       2           2         0                0         0         2        0           0      0.00       198.00          0.00
  76  shayans                   8       1           0         1                0         1         0        0           0     99.00        99.00          0.00
  96  regananson                5       3           3         0                0         3         0        0           0      0.00       248.00          0.00
  </code></pre>
  
  <p>the pdPotRev is using the exact same functions in the exact same way and works perfectly.</p>