title: How can I reference an aggregate column in another column in the same query?
body: <p>I have a query that looks more or less like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>where <code>Amount</code> is a currency (stored as a varchar, without a decimal), <code>RemittingMember</code> is a link between the <code>Items</code> table and the <code>Member</code> table and <code>RecordId</code> indicates what type of item the row is. In this case, a 50 is a debit and a 10 is a credit.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>a.Number</code> will change which will involve iteration which I want to avoid.</p>
  
  <p>I also need to check the sign of this third column to put a value in a fourth column.</p>
  
  <p>Is there a clever way of achieving this?</p>