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;id clicks shows
35 196 196
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.idSELECT 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;
Средний
Средний
