Sql query with if statment

Question

I am trying to find a query for an income statement. This will require two tables: clicks and offers. Revenue is calculated by the number of conversions * commission per offer. Conversions are stored in the click table in a field called "conversionDate", and the fee for each offer is stored in the offer table.

The request must have a condition to ignore any clicks that are not converted (which means that convertDate is NULL) when adding revenue to the offer.

I have a need for customization, as it does not provide the correct value for revenue:

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

I have 3 dummy click posts right now, 2 of which are conversions. If the commission is set to 1, the income should be equal to 2. The result that I get is equal to 1. Am I on the right track or should the calculation of income be some kind of subquery or what?

Answer 1

I would write a 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 is another solution, but offers that do not have converted clicks are not displayed as a result of the request:

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;

Answer 2

move null check to WHERE clause

Answer 3

:

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 4

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;