title: Total Sum and Partial Sum
body: <p>I am currently using SSMS. I am pulling data, and trying to get two different columns that sum prices. The two columns 'ChangeSpend' and 'TotalSpend' both reference the same column and this is where I am running into problems.</p>
  
  <p>I want ChangeSpend to return the sum of all the codes per receipt that start with V.Ch% (so they exclude all the others) and the TotalSpend to sum all of the codes for each receipt.</p>
  
  <p>Here is my current code:</p>
  
  <pre><code>SELECT 
        Receipt
        ,ReceiptCode
        ,ReceiptAmount
        ,sum(ReceiptAmount) over (Partition by Receipt) as TotalSpend
        ,(CASE WHEN ReceiptCode = 'V.Ch%' then sum(ReceiptAmount) 
                                               over (Partition by Receipt) 
        ELSE 0
        END) as ChangeSpend
    FROM tableA
    LEFT OUTER JOIN tableB
    on A.Receipt = B.Receipt
    WHERE ReceiptCode LIKE 'V.%'
    ORDER BY Receipt
  </code></pre>
  
  <p>However, my query currently prints this:</p>
  
  <pre><code>Receipt    ReceiptCode    ReceiptAmount    TotalSpend    ChangeSpend
  1          v.cha          5                20            0         
  1          v.rt           2                20            0
  1          v.chb          6                20            0
  1          v.abc          7                20            0
  2          v.cha          20               21            0
  2          v.abc          1                21            0
  3          v.cha          4                14            0
  3          v.chb          1                14            0
  3          v.tye          7                14            0
  3          v.chs          2                14            0
  </code></pre>
  
  <p>And I would like it to print this:</p>
  
  <pre><code>Receipt    ReceiptCode    ReceiptAmount    TotalSpend    ChangeSpend
  1          v.cha          5                20            11         
  1          v.rt           2                20            11
  1          v.chb          6                20            11
  1          v.abc          7                20            11
  2          v.cha          20               21            20
  2          v.abc          1                21            20
  3          v.cha          4                14            7
  3          v.chb          1                14            7
  3          v.tye          7                14            7
  3          v.chs          2                14            7
  </code></pre>
  
  <p>Thanks for any help</p>