SQL Subtract Column Values Based on Second Column Value with Group By Statement

Question

I have an SQL statement that looks so:

select FY_CD, PD_NO, INPUT, SUM(HOURS) 
from LABOR_TABLE
group by PD_NO, INPUT;

Returning this:

FY_CD|PD_NO| INPUT |    HOURS

2008    1   Actuals     61000
2008    1   Baseline    59000
2008    2   Actuals     54000
2008    2   Baseline    59000
2008    3   Actuals     60000
2008    3   Baseline    70000

I'm trying to figure out how to subtract the Actual values from the Baseline values for each period, returning the following:

FY_CD|PD_NO| INPUT |    HOURS

2008    1   Variance    2000
2008    2   Variance    -5000
2008    3   Variance    -10000

Any help is appreciated.

Answer 1

You can actually calculate it directly by using CASE to check the value of Input,

SELECT  FY_CD, 
        PD_NO, 
        'Variance' INPUT, 
        SUM(CASE WHEN Input = 'Actuals' THEN HOURS ELSE -1 * HOURS END) HOURS
FROM    LABOR_TABLE
GROUP   BY PD_NO

• SQLFiddle Demo