Calculating variances within a column

Note: 1st table is the current dataset, 2nd table is the requested result.

I was thinking of creating temporary table X and Y which has sales and Sales&Service values data respectively and calculate the variance using a join. I was wondering if that’s the right/convenient way to solve this?

Answer

I think you just want conditional aggregation:

select region,
       sum(case when type = 'Sales' then - Amount
                when type = 'Sales&Service' then Amount
           end) as service_only
from t
group by region;

If you want an overall total, then that depends on your database. Many support rollup or grouping sets which allows this as part of the aggregation.