StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLeft Joins and Aggregate functions using count and if
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload