SUM in SQL Server with PARTITION BY clause

Question

I have the following table

QuotationId QuotationDetailId   DriverId    RangeFrom   RangeTo FixedAmount UnitAmount
-------------------------------------------------------------------------------------------
    10579      7                   1           1          1     1154.00      0.00
    10579      7                   2           2          2     1731.00      0.00
    10579      11                  1           0         10     0.00         88.53
    10579      11                  2           11        24     885.30       100.50
    10579      11                  3           25        34     2292.30      88.53

I need to write a query in SQL Server with the following logic,

The grouping is QuotationId + QuotationDetailId.
For each of this block I need to sum from the second line on the value of the previous line for fixed
```
Amount + UnitAmount * RangeFrom + FixedAmount of the current row
```

So in this case the resulting output should be

  QuotationId QuotationDetailId   DriverId    RangeFrom   RangeTo   FixedAmount  UnitAmount
10579             7                1           1           1        1154.00    0.00
10579             7                2           2           2        2885.00    0.00
10579             11               1           0           10       0.00       88.53
10579             11               2           11          24       1770.60    100.50
10579             11               3           25          34       7174.90    88.53

I've tried several queries but without success, can someone suggest me a way to do that ?

Best regards Fabrizio

SQL tables represent *unordered* sets. There is no "previous" row unless a column specifies the ordering. What is the ordering? And the resulting output is exactly the same as the input data. — Gordon Linoff, Aug 12 '16 at 14:30
The math doesn't look right. I think maybe you're missing `Amount`? — shawnt00, Aug 12 '16 at 14:48

score 2 · Answer 1 · answered Aug 12 '16 at 14:33

In SQL Server 2012+, you can do a cumulative sum. I'm not sure exactly what the logic is you want, but this seems reasonable given the data set:

select t.*,
       sum(FixedAmount*UnitAmount) over (partition by QuotationId, QuotationDetailId
                                         order by DriverId
                                        ) as running_sum
from t;

score 0 · Answer 2 · answered Aug 12 '16 at 15:07

you can use a subquery, your 'amount' column would appear on the list of columns as a query in brackets,

SELECT ...fields..., 
       (SELECT SUM(A.unitAmount * A.RangeFrom + A.fixedAmount) 
               From YourTable A
               WHERE A.QuotationId = B.QuotationId 
               AND   A.QuotationDetailId = B.QuotationDetailId
               AND   A.DriverId <= B.DriverId) AS Amount
        From YourTable B

SUM in SQL Server with PARTITION BY clause

2 Answers2