Link to home
Start Free TrialLog in
Avatar of Paul Brooks
Paul BrooksFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of OnALearningCurve
OnALearningCurve

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.
ASKER CERTIFIED SOLUTION
Avatar of OnALearningCurve
OnALearningCurve

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the points.

Glad I could help.

Mark.