CODEWITHSUNDEEP
sqlsqlite
jon
jon

Reputation: 37

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.

Upvotes: 0

Views: 697

Answers (1)

Mike W.
Mike W.

Reputation: 1375

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

Upvotes: 1

Related Questions