SQL query – Sum of total refund amount

All we need is an easy explanation of the problem, so here it is.

I want to get the total refund amount in the last 365 days. However, This return the total refund amount (not last 365 days only). What should be the right way to do this?

     SELECT  sum(a.total_amount) AS TotalRefund365Days
     from  [dbo].[UserPurchase] a
           inner join [dbo].[UserPurchaseRefunds] b 
                   on a.user_purchase_id = b.user_purchase_id
     where a.is_refunded = 1 AND b.DateCreated >  (getdate() - 365)

[dbo].[UserPurchase]
SQL query - Sum of total refund amount

[dbo].[UserPurchaseRefunds]
SQL query - Sum of total refund amount

Note: ([dbo].[UserPurchaseRefunds].Amount is total purchase quantity which is not required)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I think your issue is what David Browne pointed out in your other post regarding this, which is your relationship between the dbo.UserPurchase and dbo.UserPurchaseRefunds is one-to-many. This results in your current query are double counting the Total_Amount.

What you want to do is use a CTE or subquery to de-dupe the redundant rows first, then aggregate the remaining rows, like so:

SELECT SUM(RefundedAmounts.total_amount) AS TotalRefund365Days
FROM
(
    SELECT a.total_amount
    FROM dbo.UserPurchase a
    INNER JOIN dbo.UserPurchaseRefunds b 
        ON a.user_purchase_id = b.user_purchase_id
    WHERE a.is_refunded = 1 
        AND b.DateCreated >  (GETDATE() - 365)
    GROUP BY a.user_purchase_id, a.total_amount
) AS RefundedAmounts

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply