Subtracting alias from another alias?

Question

Trying to do some basic math here but can't get this thing to work... I want to compare a counted value based on a set of criteria, and then compare this counted value to the same list of criteria but with one less variable.

 SELECT Testa-TestB FROM(
    (SELECT count(loanflag) AS Testa FROM Data
      WHERE declinegroup="XYZ"
        AND orginalrating="A"
        AND score="724-747"
        AND mode="Open"
        AND delqdays>"0")

    (SELECT count(loanflag) AS Testb FROM Data
      WHERE declinegroup="XYZ"
        AND orginalrating="A"
        AND score="724-747"
        AND mode="Open"))

I think i've been working on this too long and am missing something easy blah!

Answer 1

In you want direct substraction

in SQL Server

SELECT 
    (
        SELECT count(loanflag) 
        FROM Data
        WHERE 
            declinegroup="XYZ"
            AND orginalrating="A"
            AND score="724-747"
            AND mode="Open"
            AND delqdays>"0"
    ) - (
        SELECT count(loanflag) 
        FROM Data
        WHERE declinegroup="XYZ"
        AND orginalrating="A"
        AND score="724-747"
        AND mode="Open"
    )
;

Alternatively you could assign the values to a variable first then substract them later

DECLARE testa int;
DECLARE testb int;

SET testa = (
        SELECT count(loanflag) 
        FROM Data
        WHERE 
            declinegroup="XYZ"
            AND orginalrating="A"
            AND score="724-747"
            AND mode="Open"
            AND delqdays>"0"
    );

SET testb = (
        SELECT count(loanflag) 
        FROM Data
        WHERE declinegroup="XYZ"
        AND orginalrating="A"
        AND score="724-747"
        AND mode="Open"
    );

Select (testa - testb);