Post reply

Only select total of 3 columns if total > 0

  • January 17, 2014 at 3:57 am

    #284637

    Hi,

    I have this sql.

    SELECT

    WIMAgedBalances.dbPatID, WIMAgedBalances.dbPatFirstName, WIMAgedBalances.dbPatLastName,

    WIMStaff.dbStaffLastName, WIMAgedBalances.Tot30d, WIMAgedBalances.Tot60d, WIMAgedBalances.Tot90d,

    SUM(WIMAgedBalances.Tot30d) + SUM(WIMAgedBalances.Tot60d) + SUM(WIMAgedBalances.Tot90d) AS TotDue

    FROM

    WIMAgedBalances, WIMStaff

    WHERE

    WIMAgedBalances.TotTot > 0

    AND WIMAgedBalances.dbDocID = WIMStaff.dbStaffCnt

    GROUP BY

    WIMAgedBalances.dbPatID,WIMAgedBalances.dbPatFirstName, WIMAgedBalances.dbPatLastName, WIMStaff.dbStaffLastName,

    WIMAgedBalances.Tot30d, WIMAgedBalances.Tot60d, WIMAgedBalances.Tot90d

    I wish to only bring back rows where the TotDue is > 0. I tried adding a 3rd additional clause to the Where statement (AND TotDue > 0) and it cuts down the rows brought back from 350 to 72 but most of those rows still have TotDue > 0 (only 11 are > 0)

    bit confused, thanks

    thanks,

  • January 17, 2014 at 3:59 am

    #1681013

    Add HAVING SUM(WIMAgedBalances.Tot30d) + SUM(WIMAgedBalances.Tot60d) + SUM(WIMAgedBalances.Tot90d) > 0 after the GROUP BY.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2014 at 4:10 am

    #1681017

    Top draw! Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply