As 2 consultas a seguir estão funcionando conforme o esperado e mostram a contagem. É possível mesclá-los em uma única consulta? Preciso que as colunas sum_hits façam parte do restante da coluna mostrada na segunda consulta.
SELECT date_time, SUM(diac.requests) AS sum_hits
FROM ox_data_summary_ad_hourly diac
JOIN ox_zones z ON (z.zoneid = diac.zone_id)
JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
AND p.agencyid = '3'
AND z.zone_type in ('application')
AND z.delivery in (8)
GROUP BY date_time
SELECT s.date_time AS date_time,
SUM(s.requests) AS sum_requests,
SUM(s.impressions) AS sum_views,
SUM(s.clicks) AS sum_clicks,
SUM(s.conversions) AS sum_conversions,
SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
SUM(s.total_revenue) AS sum_revenue,
SUM(s.total_revenue) AS sum_spend,
SUM(s.total_basket_value) AS sum_bv,
SUM(s.total_num_items) AS sum_num_items,
SUM(s.total_pubrevenue) AS sum_pubrevenue
FROM ox_data_summary_ad_hourly AS s
INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
WHERE a.agencyid = 3
AND z.zone_type = 'application'
AND z.delivery = 8
AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
GROUP BY date_time
