CODEWITHSUNDEEP
sqlsql-servert-sqlconditional-aggregation
User_K47
User_K47

Reputation: 65

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

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

Upvotes: 1

Views: 71

Answers (2)

nbk
nbk

Reputation: 49373

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

Upvotes: 0

SelVazi
SelVazi

Reputation: 16033

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

Upvotes: -1

Related Questions