title: sql query with if statment
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>What I've got needs a bit of tweaking as it is not giving the correct value for revenue:</p>
  
  <p>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;</p>
  
  <p>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?</p>