How to get the sum of column in Table2 where table1 meet condition

Question

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

Answer 1

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

fiddle

Answer 2

You can do it as follows :

select Sum(CASE WHEN Orders.Status  = 'Credit'  THEN 1 ELSE 0 END) as NoOfCreditor, 
Sum(CASE WHEN Orders.Status  = 'Credit'  THEN  OrderDetails.Amount ELSE 0 END) as AmountTotal
from Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID