Search code examples
sqlsumaggregate

sum a value that appears on multiple rows ones per id in microsoft sql

I have a table that looks like below. In my query I'm sum:ing the order item value for each order item type, but I'm not sure how to summarize the providerfee per order id when the providerfee appears on multiple rows. Sum(providerfee) would give me 60 instead of 30.

Raw table (sample):

order id order item type order item value providerfee
1 Product 300 30
1 Shipping 40 30
1 Invoicefee 30 30

Aggregated table if I would query above example table with the query I'm using now:

noOfOrders productAmount ShippingAmount InvoiceAmount providerfee
1 300 40 30 60

What value I wish to get in the providerfee column in my aggregated table instead:

noOfOrders productAmount ShippingAmount InvoiceAmount providerfee
1 300 40 30 30

Does anyone now best practice of fetching the right sum per order id from the providerfee-column? I think it won't make any sense to publish the actual query I'm writing but basically what I'm doing to fetch the amounts for order item types is:

    with amounts as (
        select
        case when orderItemType= 'Product' then orderItemValue else 0 end as productAmount
        ,case when orderItemType = 'Shipping' then orderItemvalue else 0 end as shippingAmount
        case when orderItemType = 'Fee' then orderItemvalue else 0 end as InvoicefeeAmount
        ,providerfee
        
    from exampleTable
    
    )
    
         select
         sum(amounts.productAmount) as productAmount
         ,sum(amounts.shippingAmount) as shippingAmount
         ,sum(amounts.invoicefeeAmount) as invoicefeeAmount
         ,sum(amounts.providerfee) as providerfeeAmount <---- This one gives me too high values since I'm summing every rows and providerfee appears on every row 

        from amounts

Here's a picture of how my actual table look like. Providerfee is supposed to be 638 just like invoiceAmount enter image description here

One thing I also tried was a logic with row number function. But this gave me a result where three providerfees where missing. Instead of 638 I got 551. Can't see why since I counted in the raw table and got it to 638 myself. Not sure if I'm missing something logical with row number function that can have an impact on the SUM. Should I try another function to be able to pull out only one providerfee-row per order id?

    with amounts as (
        select
        row_number() over (partition by orderId order by orderItemTimestamp DESC) as rn
        ,case when orderItemType= 'Product' then orderItemValue else 0 end as productAmount
        ,case when orderItemType = 'Shipping' then orderItemvalue else 0 end as shippingAmount
        ,case when orderItemType = 'Fee' then orderItemvalue else 0 end as InvoicefeeAmount
        ,providerfee
        
    from exampleTable
    
    )
    
         select
         sum(amounts.productAmount) as productAmount
         ,sum(amounts.shippingAmount) as shippingAmount
         ,sum(amounts.invoicefeeAmount) as invoicefeeAmount
         ,sum(amounts.providerfee) as providerfeeAmount <---- This one gives me too high values since I'm summing every rows and providerfee appears on every row 
         ,sum(case when (amounts.providerfee is not null and amounts.rn = 1) then amounts.providerfee else 0 end) as providerfee2

        from amounts
Solution

  • Grand total can be done using conditional aggregation. (Note that I here expect every Orderto have exactly one Invoicefee.)

    select COUNT(distinct OrderId) as noOfOrders,
       SUM(case when orderItemType = 'Product'  then orderItemValue else 0 end) as productAmount,
       SUM(case when orderItemType = 'Shipping' then orderItemvalue else 0 end) as shippingAmount,
       SUM(case when orderItemType = 'Fee'      then orderItemvalue else 0 end) as InvoicefeeAmount,
       SUM(case when orderItemType = 'Fee'      then providerfee    else 0 end) as providerfee
from amounts

    You can also add a GROUP BY, to get aggregation per order:

    select OrderId,
       SUM(case when orderItemType = 'Product'  then orderItemValue else 0 end) as productAmount,
       SUM(case when orderItemType = 'Shipping' then orderItemvalue else 0 end) as shippingAmount,
       SUM(case when orderItemType = 'Fee'      then orderItemvalue else 0 end) as InvoicefeeAmount,
       SUM(case when orderItemType = 'Fee'      then providerfee    else 0 end) as providerfee
from amounts
group by OrderId