Updating a sql table with a sumifs equivalent query

Question

Hope you can help. I'm fairly new to sql, and this is the query that I have.

I want to update an existing table with a new field that sums the orders for each unique number on each date. So for example, in the following table, SumOfOrders is the column I want.

UniqueID    Date    Orders  SumOfOrders
A       01/03/2012      2       9
A       01/03/2012      3       9
A       01/03/2012      4       9
A       01/07/2012      1       1
B       02/07/2012      3       3
B       30/10/2012      2       4
B       30/10/2012      2       4
C       01/03/2012      5       5
D       NULL            NULL    n/a

There are over a million unique IDs, so this is just a simplified example.

From what I googled, I felt that it should use some variant on sum over partition by (using update table and set, but the following code gives the wrong number of records:

select [UniqueID], [Date], [Orders], 
  sum([Orders]) over (partition by [UniqueID], [Date] order by [UniqueID], [Date])as SumOfOrders
            from test
            group by [UniqueID], [Date], [Orders]
            order by [UniqueID], [Date], [Orders]

Any pointers would be very appreciated!

Answer 1

Perhaps you just want :

select [UniqueID], [Date], [Orders], 
        sum([Orders]) over (partition by [UniqueID], [Date]) as SumOfOrders
from test
order by [UniqueID], [Date], [Orders];  

The same simplified version would be :

select t.[UniqueID], t.[Date], t.[Orders], t1.SumOfOrders
from test t cross apply 
     (select sum(t1.Orders) as SumOfOrders
      from test t1
      where t1.[UniqueID] = t.[UniqueID] and t1.[Date] = t.[Date]
     ) t1
order by t.[UniqueID], t.[Date], t.[Orders];