Get all customers whose total spendings are $1000 or more-CodePudding

I'd like to retrieve the customers (ID and name) who have spent at least $1000.

The tables concerned have the following simplified shape :

Sales : {SaleID, CustomerID, Amount}
Customers : {CustomerID, CustomerName}

This query gives me what I want, but I think having to compute the SUM twice is a sign that I'm perhaps not approaching this problem correctly.

SELECT
    t2.CustomerID, t2.CustomerName, SUM(t1.Amount) AS total_spent
FROM
    Sales t1
JOIN
    Customers t2 ON t1.CustomerID = t2.CustomerID
GROUP BY
    t2.CustomerID, t2.CustomerName
HAVING
    SUM(t1.Amount) >= 1000

CodePudding user response：

Without compute it cant filter the value. I think this is the right solution Also we have an alternative we can do this calculation and filter with CTE table. Have tried ? check both execution plan.

CodePudding user response：

What you have done is not a bad approach. Another way you can do this :

SELECT * FROM 
    (SELECT t2.CustomerID, t2.CustomerName, 
     SUM(t1.Amount) AS total_spent FROM Sales t1
     JOIN Customers t2 ON t1.CustomerID = t2.CustomerID
     GROUP BY t2.CustomerID, t2.CustomerName 
    ) AS Result
WHERE total_spent >= 1000

You can check the performance, but I think this one would be better to use.