In my Orders table I have a column Status that holds two items such as 'Paid' or 'Not paid'. In Order Details table I have Amount column. I have written a query to select the number of customer that have not paid and the sum of amount, but it is summing all amount from the Order Details table. I want to get the sum of amount for only those customers that have not paid.
Orders
| OrderID | Customer Name | Status |
|---|---|---|
| 1001 | Mary Vicker | Credit |
| 1002 | Stephanie Musa | Paid |
| 1003 | Peter Crowner | Credit |
OrderDetails
| ID | OrderID | Product | Qty | Price | Amount |
|---|---|---|---|---|---|
| 1 | 1001 | Banana | 2 | 4.00 | 8.00 |
| 2 | 1001 | Orange | 1 | 1.00 | 1.00 |
| 3 | 1001 | Apple | 5 | 3.00 | 15.00 |
| 4 | 1002 | Banana | 2 | 2.00 | 4.00 |
| 5 | 1002 | Pawpaw | 2 | 3.00 | 6.00 |
| 6 | 1003 | Apple | 7 | 3.00 | 21.00 |
select Sum(CASE WHEN Orders.Status = 'Credit' THEN 1 ELSE 0 END) as NoOfCreditor,
Sum(OrderDetails.Amount) as AmountTotal
from Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
From the table above the expected out put should be NoOfCreditor = 2 AmountTotal = 45.00
I'm getting 55.00 as AmountTotal because it sums all. Kindly assist me to write query to get Amount total for credit only
a better approach would be
but your schema is not normalized and the name belongs into a customer table
select COUNT(DISTINCT Orders.[OrderID] ) as NoOfCreditor,
Sum(OrderDetails.Amount) as AmountTotal
from Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE [Status] = 'Credit'
| NoOfCreditor | AmountTotal |
|---|---|
| 2 | 45.00 |