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?

Bill Karwin · Accepted Answer · 2009-04-12 23:38:31Z

6

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;

answered Apr 12, 2009 at 23:38

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Griffin Over a year ago

The first query is what I needed. Thanks alot!

Steven A. Lowe · Accepted Answer · 2009-04-12 22:01:09Z

0

move the null check to the WHERE clause

answered Apr 12, 2009 at 22:01

Steven A. Lowe

61.4k19 gold badges136 silver badges206 bronze badges

2 Comments

Brian Griffin Over a year ago

That would work for this specific example, but the query I'm actually using has much more info in it; I can't exclude the clicks that did not convert as it would then mess up the other data.

Steven A. Lowe Over a year ago

@[wbgriffin]: split it into 2 sub/queries, or post the real thing; can't answer questions accurately using incomplete/misleading examples ;-)

Gumbo · Accepted Answer · 2009-04-12 22:03:31Z

0

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;

answered Apr 12, 2009 at 22:03

Gumbo

657k112 gold badges792 silver badges852 bronze badges

Comments

Steve Willcock · Accepted Answer · 2009-04-12 23:01:07Z

0

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;

answered Apr 12, 2009 at 23:01

Steve Willcock

27.1k4 gold badges47 silver badges40 bronze badges

Collectives™ on Stack Overflow

sql query with if statment

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related