combine multiple sql with same FROM clause and diff where clause

Question

I am relatively new when working with queries so let me apologize in advance.

my sql is

$sql1 = SELECT 
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_international
 
FROM `event_contract` 
LEFT JOIN `event` ON `event_id` = `event_contract_event_id` 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE `event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US'

$sql2 = SELECT 
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_domestic 

FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE `event_bid_transportation_method` = 'airplane' AND event_location_country = 'US' AND user_location_country = 'US'

$sql3 = SELECT 
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_driven 

FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE `event_bid_transportation_method` IN ('auto', 'train')

When looked closely the from part and left join part is common, I want to combine in a way that

SELECT

AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_international,

AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, 
`user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_domestic,


AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_driven   

FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE where1 of $sql1, where2 of $sql2, where3 of $sql3

Is there any way to do this? Thanks

Answer 1

The most important issue is to ensure that the aggregations return the same results in the combined query as they do in the separate queries.

You achieve the correctness by making your aggregations conditional, i.e. by putting the conditions used in the separate queries inside the corresponding AVG calls. For example, if your first query aggregates the data like this:

SELECT
  AVG( ST_Distance_Sphere... ) as market_miles_flown_international
  ...
WHERE
  `event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US'

then the combined query should calculate that value like this:

SELECT
  AVG( CASE
         WHEN `event_bid_transportation_method` = 'airplane'
          AND event_location_country <> 'US'
         THEN ST_Distance_Sphere...
       END ) as market_miles_flown_international
  ...

Note the CASE conditional, and that the WHEN condition is exactly the same as the one the first query uses. Using this conditional, the distance is aggregated only for the rows matching the condition – same as the first query is doing, but now the query is working on a rowset that includes other rows as well, therefore you need to add this filter specifically at the time of aggregation.

All your AVG calls should be modified like that.

Whether you should also repeat all those conditions in the combined query's WHERE clause is less important as far as the accuracy of the results is concerned, although it may be important in terms of performance. If the sum of those conditions filters out the underlying set substantially, then repeating them in WHERE may be beneficial. If you do decide to include them, combine them with OR:

...
WHERE
     ( `event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US' )
  OR ( `event_bid_transportation_method` = 'airplane' AND event_location_country = 'US' AND user_location_country = 'US' )
  OR ( `event_bid_transportation_method` IN ('auto', 'train') )

There is one other suggestion I would like to offer. Your combined query will have to have three separate AVG calls for each piece of data. Each of those calls will be aggregating the same ST_Distance_Sphere expression, albeit under different conditions. It is possible to avoid repetition of the complex expression with the help of a lateral join, like this:

SELECT
  AVG( CASE
         WHEN `event_bid_transportation_method` = 'airplane'
          AND event_location_country <> 'US'
         THEN x.miles
       END ) as market_miles_flown_international,
  AVG( CASE
         WHEN `event_bid_transportation_method` = 'airplane'
          AND event_location_country = 'US'
          AND user_location_country = 'US'
         THEN x.miles
       END ) as market_miles_flown_domestic,
  AVG( CASE
         WHEN `event_bid_transportation_method` IN ('auto', 'train')
         THEN x.miles
       END ) as market_miles_driven   
FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id`
CROSS JOIN LATERAL
(
  SELECT
    ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` )
                      , point( `user`.`user_location_longitude`, `user`.`user_location_latitude` )
                      ) * .000621371192
) AS x (miles)
WHERE  /* WHERE is unnecessary for accuracy but might be necessary for performance */
     ( `event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US' )
  OR ( `event_bid_transportation_method` = 'airplane' AND event_location_country = 'US'
                                                      AND user_location_country = 'US' )
  OR ( `event_bid_transportation_method` IN ('auto', 'train') )
;

This way, the expression for distance will be defined only once. It will be calculated for every row, but aggregated conditionally.

Answer 2

You can do this by combining the WHERE clauses

SELECT 
AVG(ST_Distance_Sphere(
    point(`event`.`event_location_longitude`,`event`.`event_location_latitude`),
    point(`user`.`user_location_longitude`,`user`.`user_location_latitude`)) 
    *.000621371192) as market_miles_flown_international
FROM `event_contract` 
LEFT JOIN `event` ON `event_id` = `event_contract_event_id` 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 
WHERE
(`event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US')
OR (`event_bid_transportation_method` = 'airplane'
    AND event_location_country = 'US'
    AND user_location_country = 'US')
OR (`event_bid_transportation_method` IN ('auto', 'train'));

SUGGESTION

Change all LEFT JOIN clauses to INNER JOIN

SELECT 
AVG(ST_Distance_Sphere(
    point(`event`.`event_location_longitude`,`event`.`event_location_latitude`),
    point(`user`.`user_location_longitude`,`user`.`user_location_latitude`)) 
    *.000621371192) as market_miles_flown_international
FROM `event_contract` 
INNER JOIN `event` ON `event_id` = `event_contract_event_id` 
INNER JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
INNER JOIN `user` ON `user_id` = `event_bid_td_id` 
WHERE
(`event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US')
OR (`event_bid_transportation_method` = 'airplane'
    AND event_location_country = 'US'
    AND user_location_country = 'US')
OR (`event_bid_transportation_method` IN ('auto', 'train'));