title: Reuse aggregate function value multiple times in the same query in sql
body: <p>I am working on a transaction report export from our system to another, and have a query like the following</p>
  
  <pre><code>SELECT {otherFields}, 
  CASE WHEN SUM(CASE WHEN FLAG='D' then -amount else amount) &gt; 0 
  THEN SUM(CASE WHEN FLAG='D' then -amount else amount) ELSE 0 AS DEBIT
  CASE WHEN SUM(CASE WHEN FLAG='C' then -amount else amount) &lt;= 0 
  THEN -SUM(CASE WHEN FLAG='D' then -        amount else amount) ELSE 0 AS CREDIT
  FROM table INNER JOIN .....
  </code></pre>
  
  <p>The query works as expected however i have conducted the same sum 4 times, first to determine if its a net debit or credit on the account then again to get insert that net amount into the debit or credit columns.</p>
  
  <p>Is there a way to capture the value of the sum in the select cause and have it reused (or sql server smart enough to cache it) so that my query would be something like. </p>
  
  <pre><code>    CASE WHEN @NET_TOTAL &gt; 0 THEN @NET_TOTAL ELSE 0 AS DEBIT,
      CASE WHEN @NET_TOTAL &lt;= 0 THEN @NET_TOTAL ELSE 0 AS CREDIT
  </code></pre>