How can I reference an aggregate column in another column in the same query?

Question

I have a query that looks more or less like this:

INSERT INTO #results
SELECT Name, 
        (SELECT 
            SUM(CAST(Amount AS BIGINT)) 
         FROM Items 
         WHERE RemittingMember = a.Number 
            and RecordId = 50), 
        (SELECT SUM(CAST(Std_Amount AS BIGINT)) 
         FROM Items 
         WHERE RemittingMember = a.Number 
            AND RecordId = 10)
FROM Member a

where Amount is a currency (stored as a varchar, without a decimal), RemittingMember is a link between the Items table and the Member table and RecordId indicates what type of item the row is. In this case, a 50 is a debit and a 10 is a credit.

I need to subtract the first column from the second column and have that value in a third column. I know I can do this as follows:

INSERT INTO #results
SELECT Name, 
            (SELECT 
                SUM(CAST(Amount AS BIGINT)) 
             FROM Items 
             WHERE RemittingMember = a.Number 
                and RecordId = 50), 
            (SELECT SUM(CAST(Std_Amount AS BIGINT)) 
             FROM Items 
             WHERE RemittingMember = a.Number 
                AND RecordId = 10),
             (SELECT 
                SUM(CAST(Amount AS BIGINT)) 
             FROM Items 
             WHERE RemittingMember = a.Number 
                and RecordId = 50) - (SELECT SUM(CAST(Std_Amount AS BIGINT)) 
             FROM Items 
             WHERE RemittingMember = a.Number 
                AND RecordId = 10)    
FROM Member a

But this is rather difficult to read and cumbersome to change if changes ever need to be made. I also know I can do this with local variables, but this is a report where a.Number will change which will involve iteration which I want to avoid.

I also need to check the sign of this third column to put a value in a fourth column.

Is there a clever way of achieving this?

Serg · Accepted Answer · 2013-07-10T13:37:54.773

2

You can use CTE, something like this:

;WITH intermediate_results (name, debit, credit)
AS (
SELECT
    Name
    AS name, 

    (SELECT 
        SUM(CAST(Amount AS BIGINT)) 
     FROM Items 
     WHERE RemittingMember = a.Number 
        and RecordId = 50)
    AS debit, 

    (SELECT SUM(CAST(Std_Amount AS BIGINT)) 
     FROM Items 
     WHERE RemittingMember = a.Number 
        AND RecordId = 10)
    AS credit

FROM Member a
)

INSERT INTO #results
SELECT name, debit, credit, debit - credit, SIGN(debit - credit)
  FROM intermediate_results

edited Jul 10 '13 at 13:37

answered Jul 10 '13 at 13:02

Serg

2,346
3
29
38

*Update:* Added fifth column to resulting `SELECT` statement – Serg Jul 10 '13 at 13:39

i-one · Answer 2 · 2013-07-10T13:31:29.203

1

You can use outer apply statement:

INSERT INTO #results
SELECT Name, 
        s1.Value, 
        s2.Value,
        s2.Value - s1.Value,
        case
            when s2.Value > s1.Value then 1
            when s2.Value < s1.Value then -1
            else 0
        end
FROM Member a
    outer apply (SELECT 
            SUM(CAST(Amount AS BIGINT)) Value
         FROM Items 
         WHERE RemittingMember = a.Number 
            and RecordId = 50) s1
    outer apply (SELECT 
            SUM(CAST(Std_Amount AS BIGINT)) Value
         FROM Items 
         WHERE RemittingMember = a.Number 
            and RecordId = 10) s2

edited Jul 10 '13 at 13:31

answered Jul 10 '13 at 12:55

i-one

5,050
1
28
40

You were first but a CTE is faster in my case :) – ldam Jul 10 '13 at 13:23
As I answered about CTE I like this answer too. BTW, maybe it was slower because of calculating sign column. `CASE` is too slow, try to use [`SIGN()` function](http://msdn.microsoft.com/en-us/library/ms188420%28v=sql.105%29.aspx) Also, add the fifth column to CTE answer and run your test again :-) – Serg Jul 10 '13 at 13:35

score 0 · Answer 3 · answered Jul 10 '13 at 17:41

You should be able to simplify the query even more to remove an additional join onto the items table. Something like this should work. Needs to be tested with your data

SELECT
    name, 
    debit, 
    credit, 
    Amount = debit - credit, 
    [SIGN] = SIGN(debit - credit)
FROM Member a
inner join(
     SELECT 
        RemittingMember,
        debit=SUM(CASE WHEN RecordId = 50 THEN CAST(Amount AS BIGINT) ELSE 0 END),
        credit=SUM(CASE WHEN RecordId = 10 THEN CAST(Std_Amount AS BIGINT) ELSE 0 END)
     FROM Items 
     WHERE RecordId IN(10,50)
     GROUP BY
        RemittingMember
) as Amts
    on Amts.RemittingMember = a.Number

How can I reference an aggregate column in another column in the same query?

3 Answers3