views:

99

answers:

3

I need to combine the results for two select queries from two view tables, from which I am performing calculations. Perhaps there is an easier way to perform a query using if...else - any pointers?

Essentially I need to divide everything by 'ar.time_ratio' under the condition in sql query 1, and ignore that for query 2.

SELECT 
gs.traffic_date,
gs.domain_group,
gs.clicks/ar.time_ratio as 'Scaled_clicks',
gs.visitors/ar.time_ratio as 'scaled_visitors',
gs.revenue/ar.time_ratio as 'scaled_revenue',
(gs.revenue/gs.clicks)/ar.time_ratio as 'scaled_average_cpc',
(gs.clicks)/(gs.visitors)/ar.time_ratio as 'scaled_ctr',
gs.average_rpm/ar.time_ratio as 'scaled_rpm',
(((gs.revenue)/(gs.visitors))/ar.time_ratio)*1000 as "Ecpm"
FROM
group_stats gs, 
v_active_ratio ar

WHERE ar.group_id=gs.domain_group

and

SELECT 
gs.traffic_date,
gs.domain_group,
gs.clicks,
gs.visitors,
gs.revenue,
(gs.revenue/gs.clicks) as 'average_cpc',
(gs.clicks)/(gs.visitors) as 'average_ctr',
gs.average_rpm,
((gs.revenue)/(gs.visitors))*1000 as "Ecpm"
FROM
group_stats gs, 
v_active_ratio ar

where not ar.group_id=gs.domain_group
+3  A: 

That's usually what UNION [ALL] is for.

ggiroux
+3  A: 

Use the UNION operator. You can add another column to show which query a row came from if that's important to you.

http://dev.mysql.com/doc/refman/5.1/en/union.html

David M
A: 

The UNION suggested above will give you all scaled results, and all the non-scaled data (the second query) as separate rows.

If you want to group them on their common data, so you have the scaled and normal data side by side on the same row, you can use a query like this:

   SELECT * FROM ( [first-query] ) AS scaled
    INNER JOIN ( [second-query] ) AS normal
    ON scaled.traffic_date=normal.traffic_date 
       AND scaled.domain_group=normal.domain_group

Instead of using * to select all columns, may want to explicitly declare the columns selected, since the columns traffice_date and domain_group will be output twice.

mdma