CODEWITHSUNDEEP
sqlsql-server
Joe
Joe

Reputation: 1055

TSQL - SQL Server 2014 - Sums based on different parameters

I have a table with sales details.

Buyer can buy several items and pay all together: in this case, OrderID is the same for all items and the PaymentAmount and PaymentID too

But it can happen that they pay in 2 installments, in this case there are different PaymentID (while payment amount can be the same).

I need to check that shipping costs are correctly calculated, from difference between PaymentAmounts and Quantity*Price

Relevant columns are:

| SaleID | OrderID | Quantity | Price | PaymentAmount | PaymentID | Shippingcost |
|  0001  |   001   |    3     |  3.50 |      13.50    |    BT123  |    3.00      |
|  0002  |   002   |    1     |  1.50 |      13.50    |    BB331  |    5.50      |
|  0003  |   002   |    5     |  2.00 |      13.50    |    BB331  |    5.50      |
|  0004  |   002   |    2     |  5.00 |      13.50    |    BB332  |    5.50      |
|  0005  |   003   |    1     |  3.00 |      12.50    |    BV444  |    4.00      |
|  0006  |   003   |    1     |  5.50 |      12.50    |    BV444  |    4.00      |

therefore for the same

OrderID

I need to sum the payment amounts that have different PaymentID, and I have to sum all quantity and prices:

Therefore for OrderID 002 I would expect:

TotalPayments 27.00  
TotalGoods    21.50  
ShippingCosts  5.50

So far my solution looks like this:

declare @OrderID nvarchar(10)='0002'

select distinct
    paymentAmount,
    sum (quantitypurchased*currentprice) over (partition by OID) TotalGoods
from 
    Sales s
where 
    OrderID = @OrderID

and

declare @OrderID nvarchar(10) = '0002'

select distinct
    sum(paymentAmount) over () TotalPayments,
    sum (quantitypurchased * currentprice) over (partition by OID) TotalGoods
from 
    Sales s
where 
    OrderID = @OrderID

The problem is that the first solution does not return the totals, although have the right figures to calculate it.

But main issue is that returns more than 1 row, while for further processing I would need to have the total goods and total payments in a single row

While the second returns the totals in one row, but TotalPayments is wrong since value are added more than 1 time.

And I'm quite sure this can be "easily" achieved with a little more skills..

Can help a little?

Upvotes: 0

Views: 37

Answers (2)

Maverick Sachin
Maverick Sachin

Reputation: 883

I came up with the same logic as Gordon's answer but instead, I used CTE.

;With CTE
As
(
    Select 
        OrderID,
        Quantity,
        Price,
        PaymentAmount,
        ShippingCost,
        ROW_NUMBER() Over(Partition By OrderID, PaymentID Order By OrderID) As RowNum
    From Sales
)
Select 
    OrderID,
    Sum(Case When RowNum = 1 Then PaymentAmount End) As TotalPayments,
    Sum(Quantity * Price) As TotalCosts,
    Max(ShippingCost) As ShippingCost
From CTE
Group By OrderID
Order By OrderID;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think window functions can help you isolate the rows that need to be involved in the calculations:

select s.orderid,
       sum(case when seqnum = 1 then paymentamount else 0 end) as totalpayments,
       sum(quantity * price) as totalprice,
       max(shippingcost) as shippingcost
from (select s.*,
             row_number() over (partition by s.orderid, s.paymentid order by saleid) as seqnum
      from sales s
     ) s
group by s.orderid;

Upvotes: 1

Related Questions