select x.PRODUCTCAT_NAME, x.SALE_OFFICE_NAME ,x.AREA_NAME, sum(x.COST_AMOUNT), sum(x.COST_NUM), sum(x.DELIVERY_NUM) from (SELECT
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME,
0 DELIVERY_NUM,
sum(F_FI_COST.COST_AMOUNT) COST_AMOUNT,
sum(F_FI_COST.COST_NUM) COST_NUM
FROM
D_PRODUCT_SERIES,
D_PRODUCT_CATEGORY,
D_SALE_OFFICE,
D_AREA,
F_FI_COST,
D_PRODUCT,
D_DATE,
D_MONTH
WHERE
( D_PRODUCT_SERIES.PRODUCTCAT_ID=D_PRODUCT_CATEGORY.PRODUCTCAT_ID )
AND ( D_SALE_OFFICE.AREA_ID=D_AREA.AREA_ID )
AND ( F_FI_COST.PRODUCT_ID=D_PRODUCT.PRODUCT_ID )
AND ( D_DATE.DATE_ID=F_FI_COST.DATE_ID )
AND ( F_FI_COST.SALE_OFFICE_ID=D_SALE_OFFICE.SALE_OFFICE_ID )
AND ( D_MONTH.MONTH_ID=D_DATE.MONTH_ID )
AND ( D_PRODUCT.PRODUCTSERIES_ID=D_PRODUCT_SERIES.PRODUCTSERIES_ID )
AND
( D_MONTH.MONTH_CODE = '201107' )
GROUP BY
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME
union all
SELECT
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME,
SUM( F_SALE_DELIVERY_MONTH.DELIVERY_NUM) DELIVERY_NUM,
0 COST_AMOUNT,
0 COST_NUM
FROM
D_PRODUCT_SERIES,
D_PRODUCT_CATEGORY,
D_SALE_OFFICE,
D_AREA,
D_PRODUCT,
D_PRODUCT_BATCH,
D_MONTH,
F_SALE_DELIVERY_MONTH
WHERE
( D_PRODUCT_SERIES.PRODUCTCAT_ID=D_PRODUCT_CATEGORY.PRODUCTCAT_ID )
AND ( D_SALE_OFFICE.AREA_ID=D_AREA.AREA_ID )
AND ( D_PRODUCT.PRODUCT_ID=D_PRODUCT_BATCH.PRODUCT_ID )
AND ( D_MONTH.MONTH_ID=F_SALE_DELIVERY_MONTH.DELIVERY_MONTH_ID )
AND ( F_SALE_DELIVERY_MONTH.SALE_OFFICE_ID=D_SALE_OFFICE.SALE_OFFICE_ID )
AND ( F_SALE_DELIVERY_MONTH.PRODUCT_BATCH_ID=D_PRODUCT_BATCH.PRODUCT_BATCH_ID )
AND ( D_PRODUCT.PRODUCTSERIES_ID=D_PRODUCT_SERIES.PRODUCTSERIES_ID )
AND
( D_MONTH.MONTH_CODE = '201107' )
GROUP BY
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME) x
group by
x.PRODUCTCAT_NAME, x.SALE_OFFICE_NAME ,x.AREA_NAME
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME,
0 DELIVERY_NUM,
sum(F_FI_COST.COST_AMOUNT) COST_AMOUNT,
sum(F_FI_COST.COST_NUM) COST_NUM
FROM
D_PRODUCT_SERIES,
D_PRODUCT_CATEGORY,
D_SALE_OFFICE,
D_AREA,
F_FI_COST,
D_PRODUCT,
D_DATE,
D_MONTH
WHERE
( D_PRODUCT_SERIES.PRODUCTCAT_ID=D_PRODUCT_CATEGORY.PRODUCTCAT_ID )
AND ( D_SALE_OFFICE.AREA_ID=D_AREA.AREA_ID )
AND ( F_FI_COST.PRODUCT_ID=D_PRODUCT.PRODUCT_ID )
AND ( D_DATE.DATE_ID=F_FI_COST.DATE_ID )
AND ( F_FI_COST.SALE_OFFICE_ID=D_SALE_OFFICE.SALE_OFFICE_ID )
AND ( D_MONTH.MONTH_ID=D_DATE.MONTH_ID )
AND ( D_PRODUCT.PRODUCTSERIES_ID=D_PRODUCT_SERIES.PRODUCTSERIES_ID )
AND
( D_MONTH.MONTH_CODE = '201107' )
GROUP BY
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME
union all
SELECT
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME,
SUM( F_SALE_DELIVERY_MONTH.DELIVERY_NUM) DELIVERY_NUM,
0 COST_AMOUNT,
0 COST_NUM
FROM
D_PRODUCT_SERIES,
D_PRODUCT_CATEGORY,
D_SALE_OFFICE,
D_AREA,
D_PRODUCT,
D_PRODUCT_BATCH,
D_MONTH,
F_SALE_DELIVERY_MONTH
WHERE
( D_PRODUCT_SERIES.PRODUCTCAT_ID=D_PRODUCT_CATEGORY.PRODUCTCAT_ID )
AND ( D_SALE_OFFICE.AREA_ID=D_AREA.AREA_ID )
AND ( D_PRODUCT.PRODUCT_ID=D_PRODUCT_BATCH.PRODUCT_ID )
AND ( D_MONTH.MONTH_ID=F_SALE_DELIVERY_MONTH.DELIVERY_MONTH_ID )
AND ( F_SALE_DELIVERY_MONTH.SALE_OFFICE_ID=D_SALE_OFFICE.SALE_OFFICE_ID )
AND ( F_SALE_DELIVERY_MONTH.PRODUCT_BATCH_ID=D_PRODUCT_BATCH.PRODUCT_BATCH_ID )
AND ( D_PRODUCT.PRODUCTSERIES_ID=D_PRODUCT_SERIES.PRODUCTSERIES_ID )
AND
( D_MONTH.MONTH_CODE = '201107' )
GROUP BY
D_PRODUCT_CATEGORY.PRODUCTCAT_NAME,
D_SALE_OFFICE.SALE_OFFICE_NAME,
D_AREA.AREA_NAME) x
group by
x.PRODUCTCAT_NAME, x.SALE_OFFICE_NAME ,x.AREA_NAME
col,sum(a)-sum(b)
from
(
select col,a,b from tb
union all
select col,a,b from ta
)t
group by
col