Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Unanswered: Where clause from sum() value?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •