How to write a SQL query to get data on an advertising campaign?

Question

MySQL

Hello!
There is a small advertising account for which it is required to keep statistics: when they clicked, when they showed, data aggregation, etc.
The scheme is very simple:

I'm trying to get an ad with counted views and clicks for today's date like this:

SELECT SQL_NO_CACHE ads.id, COUNT(clicks.id) as clicks, COUNT(shows.id) as shows FROM ads
LEFT JOIN shows ON ads.id = shows.ad_id
LEFT JOIN clicks ON ads.id = clicks.ad_id
WHERE
DATE(shows.created_at) = '2014-02-24' AND
DATE(clicks.created_at) = '2014-02-24'
AND ads.id = 35;

But this result is not encouraging:

id	clicks	shows
35	196		196

For lovers of peppy interactive: sqlfiddle .
Thank you!

Answer 1

Like this:

SELECT ads.id , COUNT(DISTINCT clicks.id)  as clicks, COUNT(DISTINCT shows.id) as shows 
FROM ads
LEFT JOIN shows ON ads.id = shows.ad_id AND DATE(shows.created_at) = '2014-02-24'
LEFT JOIN clicks ON ads.id = clicks.ad_id AND DATE(clicks.created_at) = '2014-02-24'
GROUP BY ads.id

Answer 2

You can use a subquery:

SELECT ads.id,
(SELECT COUNT(*) FROM `clicks` WHERE ad_id = ads.id AND DATE(created_at) = '2014-02-24' AND
DATE(created_at) = '2014-02-24') AS clicks,
(SELECT COUNT(*) FROM `shows` WHERE ad_id = ads.id AND DATE(created_at) = '2014-02-24' AND
DATE(created_at) = '2014-02-24') AS shows
FROM ads
WHERE ads.id = 35;