Hye,
I have table Client
date idClient nameClient OpenBal open Payable dr cr comPay close
2016-10-5 CL-MK Kashif CL-MK 9000 33134 0 5000 0 0 0
2016-10-5 CL-MK Kashif CL-MK 0 33134 0 6000 0 0 0
2016-10-5 CL-MA Asim CL-MA -8000 33134 0 0 0 0 0
2016-10-5 CL-MA Asim CL-MA 0 33134 0 7000 0 0 0
2016-10-5 CL-MA Asim CL-MA 0 33134 0 0 0 0 0
2016-10-5 CL-MW Waqar CL-MW 4000 33134 0 5000 0 0 0
2016-10-5 CL-MW Waqar CL-MW 0 33134 0 0 0 0 0
2016-10-5 CL-MW Waqar CL-MW 0 33134 0 8000 0 0 0
2016-10-5 CL-MF Fahad CL-MF -7000 33134 0 0 0 0 0
2016-10-5 CL-MF Fahad CL-MF 0 33134 0 0 0 0 0
2016-10-5 CL-MF Fahad CL-MF 0 33134 0 10000 20000 150000 0
2016-10-5 CL-MF Fahad CL-MF 0 33134 0 0 0 0 0
I used this query to sum of all client
SELECT idClient,nameMemb,min(OpenBal)as OpenBal,sum(open) as open,(min(openBal)+sum(open))as able,sum(re)as re ,sum(cr) as cr,sum(comPay)as comPay,(min(openBal)+sum(open)-sum(re)-sum(cr)+sum(comPay))as close from AddClient WHERE strftime ('%m', date) = '10' group by nameMemb
in the result table the close column have 0 and negative velues I want to get value greater then zero..
I try this
SELECT idClient,nameMemb,min(OpenBal)as OpenBal,sum(open) as open,(min(openBal)+sum(open))as able,sum(re)as re ,sum(cr) as cr,sum(comPay)as comPay,(min(openBal)+sum(open)-sum(re)-sum(cr)+sum(comPay))as close from AddClient WHERE strftime ('%m', date) = '10' and close > 0 group by nameMemb
but this query not give me result.
I believe you need to use a having clause AFTER your group by. Something like:
SELECT
idClient,nameMemb,min(OpenBal)as OpenBal,sum(open) as open,(min(openBal)+sum(open))as able,sum(re)as re ,sum(cr) as cr,sum(comPay)as comPay,(min(openBal)+sum(open)-sum(re)-sum(cr)+sum(comPay))as close
from
AddClient
WHERE
strftime ('%m', date) = '10'
group by
nameMemb
having
close > 0
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments