mysql - Why doesn't my query return any rows -


structure table show here

if use query:

select idn balans outerb idn!='' group idn order  ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , status='up'), 0) - ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , status='down'), 0) desc limit 15 

i 2 rows.

but if add condition >0 :

select idn balans outerb idn!='' , ( (select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='up') - (select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='down') ) > 0 group idn order  ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='up'), 0) - ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='down'), 0) desc limit 15 

in result 0 rows...

tell me please error? why getting 0 rows?

you didn't include ifnull() in where condition. reason getting 0 rows instead of 2. sums, returned subqueries, null (that's sum() returns when there no matching row, despite 1 might think 0 more logical, returns null.)

and added condition, i'd put in having clause, not where (doesn't make difference results in case may more efficient):

select idn balans outerb idn !='' group idn  having ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='up'), 0) - ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='down'), 0) > 0 order  ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='up'), 0) - ifnull((select sum(innerb.amount) balans innerb     innerb.idn = outerb.idn     , type='down'), 0) desc limit 15 ; 

i don't see reason write query correlated subqueries. can use derived tables , simple joins (also corrected type status):

select di.idn      ( select idn       balans       group idn       having idn > ''     ) di   left join     ( select idn, sum(amount)       balans       status = 'up'       group idn     ) bu       on bu.idn = di.idn   left join     ( select idn, sum(amount) down       balans       status = 'down'       group idn     ) bd        on bd.idn = di.idn  coalesce(bu.up, 0) - coalesce(bd.down, 0) > 0 ; 

test @ sql-fiddle


Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -