SQL query - Sum of total refund amount



  • 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] [dbo].[UserPurchase]

    [dbo].[UserPurchaseRefunds] [dbo].[UserPurchaseRefunds]

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



  • 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
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2