Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The following 2 queries are working as expected and they show the count. Is it possible to merge them into single query? I need sum_hits columns to be part of the rest of the column shown in the second query.

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
share|improve this question
    
Don't use BETWEEN or >= ... AND ... <=for dates. Use >= and <, like this: s.date_time >= '2012-07-01 00:00:00' AND s.date_time < '2012-07-02 00:00:00'. You can even skip the time part with this approach: s.date_time >= '2012-07-01' AND s.date_time < '2012-07-02' –  ypercube Jul 21 '12 at 7:19
1  
Why shouldn't between used? Any reason for that? –  a_horse_with_no_name Jul 21 '12 at 17:42

1 Answer 1

up vote 3 down vote accepted
SELECT B.*,A.sum_hits
FROM
(
    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
) A INNER JOIN
(    
    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
) B USING (date_time);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.