I'm trying to add a few columns to my table and I'm a bit of the way there but not clear why it's failing. This is an example starting table...
Date Name Amount
1/2/2015 Andy 148
2/5/2015 Andy 188
2/11/2015 Andy 154
1/15/2015 John 136
2/5/2015 John 176
1/7/2015 John 134
1/19/2015 John 251
2/21/2015 Carlos 120
2/15/2015 Carlos 211
1/8/2015 Carlos 120
1/2/2014 Andy 151
2/5/2014 Andy 281
2/11/2014 Andy 298
1/15/2014 John 292
2/5/2014 John 134
1/7/2014 John 281
1/19/2014 John 101
2/21/2014 Carlos 137
2/15/2014 Carlos 108
1/8/2014 Carlos 292
I want to take the above table and...
1) Sort by Year, Name, Then Value 2) Based on #1, Add the "Ordered" column which gives a number for each set of Year and Name where Value is set sorted ascending 3) Multiplied column is multiplying Amount by Ordered 4) Sum the multiplied column and a the sum to each set
Result...
Date Year Name Amount Ordered Multiplied Sum
1/2/2014 2014 Andy 151 1 151 1607
2/5/2014 2014 Andy 281 2 562 1607
2/11/2014 2014 Andy 298 3 894 1607
2/15/2014 2014 Carlos 108 1 108 1258
2/21/2014 2014 Carlos 137 2 274 1258
1/8/2014 2014 Carlos 292 3 876 1258
1/19/2014 2014 John 101 1 101 2380
2/5/2014 2014 John 134 2 268 2380
1/7/2014 2014 John 281 3 843 2380
1/15/2014 2014 John 292 4 1168 2380
1/2/2015 2015 Andy 148 1 148 1020
2/11/2015 2015 Andy 154 2 308 1020
2/5/2015 2015 Andy 188 3 564 1020
1/8/2015 2015 Carlos 120 1 120 993
2/21/2015 2015 Carlos 120 2 240 993
2/15/2015 2015 Carlos 211 3 633 993
1/7/2015 2015 John 134 1 134 1938
1/15/2015 2015 John 136 2 272 1938
2/5/2015 2015 John 176 3 528 1938
1/19/2015 2015 John 251 4 1004 1938
I have everything but the last column as I keep getting the error...
'Invalid expression near Row_Number'.
SQL for 'Ordered'...
ROW_NUMBER() OVER ( Partition BY Name, DATEPART(YEAR, Date) ORDER BY Amount ) AS 'Ordered'
SQL for 'Multiplied'...
Amount * Ordered AS Multiplied
Now I could be thinking of this naively but I thought I could just do add a line like this...
sum(Multiplied) OVER ( Partition BY Name, DATEPART(YEAR, Date) ORDER BY Amount ) AS 'Sum'
But I keep getting the error mentioned. Any ideas how to handle? I'm welcome to hearing other ways of handling the data. I only care about the last column
If your syntax worked, it would produce a cumulative sum. That doesn't appear to be what you want.
I think you can do what you want with a subquery:
select t.*,
(seqnum * amount) as multiplied,
sum(seqnum * amount) over (partition by name, year(date)) as thesum
from (select t.*,
row_number() over (partition by name, year(date) order by date) as seqnum
from table t
) t;