zero value result array from mysql query

Question

I have the following mysql query

select registration, 
sum(`week01`) as `2015/01`, 
sum(`week02`) as `2015/02`
from (select registration, 
case when concat(YEAR(act_del_time_arrive),'/',week(act_del_time_arrive))='2015/01' then round(costed_amount,0) else '' end 'week01' , 
case when concat(YEAR(act_del_time_arrive),'/',week(act_del_time_arrive))='2015/02' then round(costed_amount,0) else '' end 'week02'
from vw_tekwani_schedule_main
                       where act_del_time_arrive between '2015-01-04' and '2015-02-28'
                       and haulier_id = 4 and registration is not null
                       group by registration, date(act_del_time_arrive)
                       order by registration) as t group by registration

which is producing the following results

reg   2015/01 2015/02
'A10'  , '0'  , '0'
'A2'   , '0'  , '0'
'A3'   , '0'  , '0'
'A4'   , '0'  , '0'
'A5'   , '0'  , '0'
'A6'   , '0'  , '0'
'A7'   , '0'  , '0'
'A8'   , '0'  , '0'
'A9'   , '0'  , '0'

I desperately need to find out how to filter the rows off that have a zero value on 2015/01 and 2015/02.

So theoretically I would not get a result being returned from this query

I have tried the filter you sugget thomas and it still returns zero value rows ;( — Terence Bruwer
– Terence Bruwer, Commented Feb 28, 2015 at 15:50

Jordi Llull · Accepted Answer · 2015-02-28 15:52:47Z

1

Just use the having clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

select registration, 
sum(`week01`) as `2015/01`, 
sum(`week02`) as `2015/02`
from (select registration, 
case when concat(YEAR(act_del_time_arrive),'/',week(act_del_time_arrive))='2015/01' then round(costed_amount,0) else '' end 'week01' , 
case when concat(YEAR(act_del_time_arrive),'/',week(act_del_time_arrive))='2015/02' then round(costed_amount,0) else '' end 'week02'
from vw_tekwani_schedule_main
                       where act_del_time_arrive between '2015-01-04' and '2015-02-28'
                       and haulier_id = 4 and registration is not null
                       group by registration, date(act_del_time_arrive)
                       order by registration) as t group by registration
HAVING sum(`week01`) > 0 OR sum(`week02`) > 0

edited Feb 28, 2015 at 15:52

answered Feb 28, 2015 at 15:25

Jordi Llull

8106 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Terence Bruwer Over a year ago

unfortunately, the having clause seems to be removing those rows where one of the two cols is zero and the other has a value

Jordi Llull Over a year ago

Oops, i just edited the HAVING condition to use an OR instead of an AND. Is it working now?

Terence Bruwer Over a year ago

sorry to throw a spanner in the works but if the very first row has a value of zero in it then all the rest are dropped as well ;(

Collectives™ on Stack Overflow

zero value result array from mysql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related