Adding a Subquery

Question

I have the following query, and I am trying to produce four columns, the buyer, source, total number of leads, and returned leads.

The issue is with including a column with the total number of leads. Returned is a column in my database where the value is either 0 or 1. So returned is everything which is 1, but for bought leads, I need everything which is 0. When I run the following query, all the bought leads end up being 0. How can I fix the subquery to get the desired results.

SELECT 
    ba.buyer, 
    l.source,
    (SELECT COUNT(*) FROM lead_status WHERE lead_id = l.id AND returned != 1) AS 'Bought Leads',
    SUM(l.returned) AS 'Returned_Leads' 
FROM lead_status AS ls 
    LEFT JOIN leads AS l ON ls.lead_id = l.id
    LEFT JOIN buyer_account AS ba ON ba.lead_buyer_id = ls.lead_buyer_id
WHERE l.origin = 'PING_POST' 
    AND ls.discriminator IN('AUTO_POST','HOME_POST','HEALTH_POST','LIFE_POST')
    AND DATE(ls.create_date) BETWEEN '2012-02-01' AND '2012-02-31'
    AND l.sold = 1 AND ls.winner = 1 AND l.test = 0 AND l.returned = 1
GROUP BY ls.buyer, l.source;

Answer 1

I would think you could do this as an alternative, if it's a binary 1 or 0:

SELECT 
    ba.buyer, 
    l.source,
    COUNT(l.returned) - SUM(l.returned) AS 'Bought Leads',
    SUM(l.returned) AS 'Returned_Leads' 
FROM lead_status AS ls 
    LEFT JOIN leads AS l ON ls.lead_id = l.id
    LEFT JOIN buyer_account AS ba ON ba.lead_buyer_id = ls.lead_buyer_id
WHERE l.origin = 'PING_POST' 
    AND ls.discriminator IN('AUTO_POST','HOME_POST','HEALTH_POST','LIFE_POST')
    AND DATE(ls.create_date) BETWEEN '2012-02-01' AND '2012-02-31'
    AND l.sold = 1 AND ls.winner = 1 AND l.test = 0 AND l.returned = 1
GROUP BY ls.buyer, l.source;

Answer 2

Just replace the subquery with:

SUM(1-l.returned)

As long as its a bit field, otherwise you should make both sums operate on a CASE statment i.e. sum (case returned when 0 then 1 else 0 end).

Answer 3

Try

SUM( abs(l.returned-1) )

• if returned has 0, it will return -1
• if returned has 1, it will return 0

taking the abs() value gives you a +1 so your SUM() should work