I need help putting two select query's into one stored procedure for one output?-CodePudding

I tried a union and union all but I get an error incorrect usage of union.

select  sr.shipper_city,
        sr.consignee_site_city,
        cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal)  as TransitDays90
from cs.shipment_reporting sr
where customer_id = '15619' 
and (pick_depart_loaded >(NOW() - INTERVAL 3 MONTH))
group by sr.shipper_city,sr.consignee_site_city
order by sr.shipper_city,sr.consignee_site_city


select sr.shipper_city,
       sr.consignee_site_city,
      (cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal))  as TransitDays30
from cs.shipment_reporting sr
where sr.customer_id = '15619' and (sr.pick_depart_loaded >(NOW() - INTERVAL 1 MONTH))
group by sr.shipper_city,sr.consignee_site_city
order by sr.shipper_city,sr.consignee_site_city

CodePudding user response：

Your queries have different column names: TransitDays90 and TransitDays30.

Based on your demands, you can either change them to the same or add NULL columns to each query, but make sure that order and number of columns is same in each query

CodePudding user response：

Do the order by in outer query, try:

select shipper_city,consignee_site_city,TransitDays90
from (
         select  sr.shipper_city,
                 sr.consignee_site_city,
                 cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal)  as TransitDays90
         from cs.shipment_reporting sr
         where customer_id = '15619' 
         and (pick_depart_loaded >(NOW() - INTERVAL 3 MONTH))
         group by sr.shipper_city,sr.consignee_site_city
                                 ---- removed order by , it has no effect
         UNION
         
         select  sr.shipper_city,
                 sr.consignee_site_city,
                 (cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal))  as TransitDays30
         from cs.shipment_reporting sr
         where sr.customer_id = '15619' and (sr.pick_depart_loaded >(NOW() - INTERVAL 1 MONTH))
         group by sr.shipper_city,sr.consignee_site_city

      ) as t1 
order by shipper_city , consignee_site_city ;