CODEWITHSUNDEEP
mysqlaggregate-functions
Layton Everson
Layton Everson

Reputation: 1148

Left Joins and Aggregate functions using count and if

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:

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`

Why is this column returning a 0 rather than the sum of t.amount: SUM(IF(p.saleType_id = 2 AND t.captured = 0, t.amount,0)) AS fwCaptured? As you can see, I do the exact same thing two lines above and it works fine.

Here is an example of the results:

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

the pdPotRev is using the exact same functions in the exact same way and works perfectly.

Upvotes: 0

Views: 111

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

Maybe t.amount is 0 for every captured sale of type 2, or it could even be that positive and negative values even out. In the field above, you're summing a different field, so it makes sense that it returns a different value. You even select from an entirely different table.

If in doubt, remove the sum and the grouping, and just select the values. You should be able to spot the problem right away.

[edit] Not true even. pdPotRev doen't use the p.captured field at all. The line that does contain the same condition is pdCapRev, which is commented out. So it's most likely that IF(p.saleType_id = 2 AND t.captured never evaluates to true.

Upvotes: 2

Related Questions