CODEWITHSUNDEEP
sqlsql-serverselect
Noah
Noah

Reputation: 19

How to select based on custom column

SELECT  
    dbo.Customers.FirstName as Firstname, dbo.Customers.LastName as Lastname, 
    dbo.Customers.Phone as Phone,  dbo.Guarantors.FirstName as Guarantor Firstname, 
    dbo.Guarantors.LastName as Guarantor Lastname, 
    dbo.Guarantors.Phone as Guarantor Phone, 
    SUM(dbo.PaymentHistory.PaymentAmount) - SUM(dbo.PaymentHistory.PayedAmount) as Debt
FROM    
    dbo.Credits 
INNER JOIN
    dbo.PaymentHistory ON dbo.Credits.ID = dbo.PaymentHistory.CreditID 
INNER JOIN
    dbo.Customers ON dbo.Credits.CustomerID = dbo.Customers.ID 
LEFT OUTER JOIN
    dbo.CreditGuarantors ON dbo.Credits.ID = dbo.CreditGuarantors.CreditID 
LEFT OUTER JOIN
    dbo.Guarantors ON dbo.CreditGuarantors.GuarantorID = dbo.Guarantors.ID
WHERE   
    (dbo.Credits.Status = 0) AND 
    (dbo.PaymentHistory.PaymentDay <= GETDATE()) AND 
    (dbo.Credits.BranchID = 1)
GROUP BY 
    dbo.Customers.LastName, dbo.Customers.Phone,
    dbo.Credits.ID, dbo.Customers.FirstName, 
    dbo.Guarantors.FirstName, dbo.Guarantors.LastName, 
    dbo.Guarantors.Phone

So I want to add 1 more rule, where Debt !=0. but I got error, can u help me with that ?? I want to get debt that is not 0, the person name who has debt

Upvotes: 0

Views: 51

Answers (2)

sagi
sagi

Reputation: 40481

Use the HAVING clause . Add this to the end of the query:

HAVING SUM(dbo.PaymentHistory.PaymentAmount) - SUM(dbo.PaymentHistory.PayedAmount) <> 0

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Because this involves aggregation functions, you want to use a HAVING clause:

HAVING SUM(dbo.PaymentHistory.PaymentAmount) - SUM(dbo.PaymentHistory.PayedAmount) <> 0

Or:

HAVING SUM(dbo.PaymentHistory.PaymentAmount) <> - SUM(dbo.PaymentHistory.PayedAmount)

Note: Your query would be easier to write and to read if you used table aliases.

Upvotes: 2

Related Questions