sql query with if statment

Question

I am trying to come up with a query to report revenue. It will require 2 tables: clicks and offers. Revenue is calculated by the number of conversions * commission for the offer. Conversions are stored in the clicks table in a field called "conversionDate", and the commission for each offer is stored in the offers table.

There needs to be a condition in the query to ignore any clicks that did not convert (meaning conversionDate is NULL) when adding up revenue for the offer.

What I've got needs a bit of tweaking as it is not giving the correct value for revenue:

SELECT o.name offer, count(c.id) clicks, if(not isnull(c.conversionDate), revenue=revenue+o.commission, revenue) revenue FROM clicks c, offers o where c.offerID=o.ID GROUP BY o.ID;

I have 3 dummy records in clicks right now, 2 of which are conversions. With the commission set to 1, revenue should be 2. The result I am getting is 1. Am I on the right track or should the revenue calculation be some kind of subquery or what?

Answer 1

move the null check to the WHERE clause

Answer 2

Try this query:

SELECT o.name offer, COUNT(c.id) clicks, IF(c.conversionDate IS NULL, revenue + o.commission, revenue) revenue
FROM clicks c, offers o
WHERE c.offerID=o.ID
GROUP BY o.ID;

Answer 3

SELECT o.name offer, count(*) clicks, (COUNT(c.ID) * o.commission) revenue
FROM clicks c, offers o
WHERE c.ConversionDate is not null and c.offerID=o.ID
GROUP BY o.ID, o.name, o.commission;

Answer 4

I'd write the query this way:

SELECT o.name AS offer, COUNT(c.id) AS clicks, 
  SUM( IF(c.conversionDate IS NOT NULL, o.commission, NULL) ) AS revenue 
FROM offers o JOIN clicks c ON (c.offerID=o.ID)
GROUP BY o.ID;

Here's another solution, but offers that don't have any converted clicks are not shown in the query result:

SELECT o.name AS offer, COUNT(c.id) AS clicks, 
  SUM(o.commission) AS revenue 
FROM offers o JOIN clicks c 
  ON (c.offerID=o.ID AND c.conversionDate IS NOT NULL)
GROUP BY o.ID;