Count the number of customers who have > 10 transactions, excluding returns?

Question

My table 'Transactions' has the following columns :

• CustID --- a unique CustomerID
• transactionID -- a unique TransactionID
• Qty --- Quantity. Values can be positive or negative. Positive values means an order was made. Negative value means the order was returned.
• total_amt --- Total_amount, cost of the total order. Value is calculated by multiplying Qty by the cost of the product.

I ran the following query and got some output, but since I don't have the expected output with me.Therefore, I want someone to help me to confirm if this code will always work and suggest edits if it doesn't.

select cust_id, COUNT(cust_id) AS Count_of_Transactions
from Transactions
where Qty >= 0
group by cust_id
having COUNT(cust_id) > 10

I don't have the expected output.

Thanks!

Answer 1

Without seeing the expected output, I think what you have is right. If you want to see the total amount summed up, you would just need to add one more column to your query:

SELECT cust_id, 
    SUM(total_amt) AS Total_Amount_of_Transactions, 
    COUNT(cust_id) AS Count_of_Transactions
FROM Transactions
WHERE Qty >= 0
GROUP BY cust_id
HAVING COUNT(cust_id) > 10