select yday.region,yday.yday,mtd.mtd,target.target from
(SELECT dr.region,sum(f.collection) as yday FROM fact_collection f,dim_time dt,dim_region dr
WHERE dt.bus_date=current_date-45 AND dt.time_id=f.time_id AND f.region_id=dr.region_id GROUP BY dr.region) as yday,
(SELECT dr.region,sum(f.collection) as mtd FROM fact_collection f,dim_time dt, dim_region dr
WHERE dt.bus_date BETWEEN date_trunc('month',current_date-45) AND current_date AND dt.time_id=f.time_id
AND f.region_id=dr.region_id GROUP BY dr.region) as mtd,
(SELECT dr.region,sum(f.target)as target FROM dim_region dr,fact_collection_target f ,dim_time dt
WHERE dt.month_id=f.month_id AND dr.region_id=f.region_id AND dt.month_id=5 GROUP BY dr.region) as target
where yday.region=mtd.region and mtd.region=target.region
union
select 'Total' as region,sum(foo.yday),sum(foo.mtd),sum(foo.target)
from
(
select yday.region,yday.yday,mtd.mtd,target.target from
(SELECT dr.region,sum(f.collection) as yday FROM fact_collection f,dim_time dt,dim_region dr
WHERE dt.bus_date=current_date-45 AND dt.time_id=f.time_id AND f.region_id=dr.region_id GROUP BY dr.region) as yday,
(SELECT dr.region,sum(f.collection) as mtd FROM fact_collection f,dim_time dt, dim_region dr
WHERE dt.bus_date BETWEEN date_trunc('month',current_date-45) AND current_date AND dt.time_id=f.time_id
AND f.region_id=dr.region_id GROUP BY dr.region) as mtd,
(SELECT dr.region,sum(f.target)as target FROM dim_region dr,fact_collection_target f ,dim_time dt
WHERE dt.month_id=f.month_id AND dr.region_id=f.region_id AND dt.month_id=5 GROUP BY dr.region) as target
where yday.region=mtd.region and mtd.region=target.region
)as foo;
the output is
"Central";144034;2594109;100394040
"East";144344;2591332;101193660
"North";144910;2591508;100558860
"South";143733;2588980;100303770
"Total";720490;12960302;502879290
"West";143469;2594373;100428960
but i want "total" to be in next to WEST