Where clause from sum() value?

RandyRiegel · #1 (**permalink**) 09-10-10, 11:06

I've been away from SQL for quite a while and am working on a DB project now. I have the following code I am trying to only get Tenants who have a negative balance.

Code:

select sum(finance.trans_amount) as balance, tenants.last, tenants.first from 
finance join tenants on (finance.trans_tenant = tenants.tenant_id)
group by finance.trans_tenant 
order by balance;

I have tried to put a "where balance < 0" in the statement but get an error about balance not being a column. Without the where clause like above everything works fine. What do I need to do to only retrieve tenants with negative balances.

r937 · #2 (**permalink**) 09-10-10, 12:28

Code:

SELECT tenants.last
     , tenants.first 
     , SUM(finance.trans_amount) AS balance
  FROM finance 
INNER
  JOIN tenants 
    ON tenants.tenant_id = finance.trans_tenant
GROUP 
    BY tenants.last
     , tenants.first 
HAVING balance < 0
ORDER 
    BY balance;

Internet Services

Web Development

Digital Marketing

Consumer Tech