jon jon - 2 months ago 11
SQL Question

How to get value greater than 0 from table in Sqlite

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.

Answer

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