I am trying to perform a complex operation where I pull the sum for an entire column of data and subtract the running subtotal from the sum for each row. I can do the component parts of Sum and Running Subtotal alone. Used this for running subtotal:
sum(UsageMetric) over(order by Nested1.IDNumber) as RunningTotal
However, I get this error when trying to comine them:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So I rigged this up, and it returns the 'stand-alone' running subtotal for a given row:
declare @TargetNumber BIGINT Set @TargetNumber=(select Nested1.IDNumber from TargetTable Nested1 where Nested1.IdNumber='1234567890' and (Extraneous Criteria Here)) select sum(Usage.UsageMetric) from Table_Usage Usage, IDTable IDT where IDT.IDNumber <= @TargetNumber and (Extraneous Criteria Here)
But what I would really like to do is be able to remove the qualifier "Nested1.IDNumber='1234567890' and just perform this for each IDNumber in TargetTable.