MySQL cross tab query: omit line if total is zero

I have a sum-if query to create a cross-tab which is working but includes row for the zero values which I want to exclude.

SELECT products.pcode AS code, products.name as name,
sum(if((order_records_future.delivery_date = '2011-04-04'),order_records_future.qty_ordered,0)) AS '04 Apr',
sum(if((order_records_future.delivery_date = '2011-04-05'),order_records_future.qty_ordered,0)) AS '05 Apr',
sum(if((order_records_future.delivery_date = '2011-04-04')
OR (order_records_future.delivery_date = '2011-04-05'), order_records_future.qty_ordered,0)) AS 'Total'
from (`order_records_future` join `products` on((`products`.`id` = `order_records_future`.`product_id`)))
where (`order_records_future`.`round_id` >= 5) AND (`order_records_future`.`round_id` <= 5)
group by products.name order by products.pcode

Result
"code";"name";"04 Apr";"05 Apr";"Total"
"0001";"568ml/Pint Wholemilk Glass Bottle";"2";"2";"4"
"0003";"568ml/Pint Skimmed Milk Glass Bottle";"0";"0";"0"
"0051";"1L Wholemilk Poly Bottle";"4";"4";"8"
"4120";"1L Blue Keld Still Water";"0";"0";"0"
"4270";"200ml JP Fairtrade Orange Juice";"0";"0";"0"

Some of the rows have zero values that I want to exclude. Have tried amending the WHERE statement but cannot find anything that works.
Paul-BrooksAsked:
Who is Participating?
 
OnALearningCurveCommented:
Sorry,

if you only want to exclude the totals of zero then use:

SELECT products.pcode AS code, products.name as name,
sum(if((order_records_future.delivery_date = '2011-04-04'),order_records_future.qty_ordered,0)) AS '04 Apr',
sum(if((order_records_future.delivery_date = '2011-04-05'),order_records_future.qty_ordered,0)) AS '05 Apr',
sum(if((order_records_future.delivery_date = '2011-04-04')
OR (order_records_future.delivery_date = '2011-04-05'), order_records_future.qty_ordered,0)) AS 'Total'
from (`order_records_future` join `products` on((`products`.`id` = `order_records_future`.`product_id`)))
where (`order_records_future`.`round_id` >= 5) AND (`order_records_future`.`round_id` <= 5)
group by products.name order by products.pcode
HAVING sum(if((order_records_future.delivery_date = '2011-04-04')
OR (order_records_future.delivery_date = '2011-04-05'), order_records_future.qty_ordered,0))  <> 0

Hope this helps,

Mark.
0
 
OnALearningCurveCommented:
HI Paul-Brooks,

try:
SELECT products.pcode AS code, products.name as name,
sum(if((order_records_future.delivery_date = '2011-04-04'),order_records_future.qty_ordered,0)) AS '04 Apr',
sum(if((order_records_future.delivery_date = '2011-04-05'),order_records_future.qty_ordered,0)) AS '05 Apr',
sum(if((order_records_future.delivery_date = '2011-04-04')
OR (order_records_future.delivery_date = '2011-04-05'), order_records_future.qty_ordered,0)) AS 'Total'
from (`order_records_future` join `products` on((`products`.`id` = `order_records_future`.`product_id`)))
where (`order_records_future`.`round_id` >= 5) AND (`order_records_future`.`round_id` <= 5)
group by products.name order by products.pcode
HAVING sum(if((order_records_future.delivery_date = '2011-04-04'),order_records_future.qty_ordered,0)) <> 0 AND sum(if((order_records_future.delivery_date = '2011-04-05'),order_records_future.qty_ordered,0)) <>0 AND sum(if((order_records_future.delivery_date = '2011-04-04')
OR (order_records_future.delivery_date = '2011-04-05'), order_records_future.qty_ordered,0))  <> 0


Hope this helps,

Mark.
0
 
OnALearningCurveCommented:
Thanks for the points.

Glad I could help.

Mark.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.