CODEWITHSUNDEEP
sql-server-2008t-sqlsubqueryaggregate-functions
ldam
ldam

Reputation: 4595

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

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?

Upvotes: 2

Views: 2000

Answers (3)

Adam Haines
Adam Haines

Reputation: 920

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

Upvotes: 0

Serg
Serg

Reputation: 2427

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

Upvotes: 2

i-one
i-one

Reputation: 5120

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

Upvotes: 1

Related Questions