Paul Brooks
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_futu re.deliver y_date = '2011-04-04'),order_record s_future.q ty_ordered ,0)) AS '04 Apr',
sum(if((order_records_futu re.deliver y_date = '2011-04-05'),order_record s_future.q ty_ordered ,0)) AS '05 Apr',
sum(if((order_records_futu re.deliver y_date = '2011-04-04')
OR (order_records_future.deli very_date = '2011-04-05'), order_records_future.qty_o rdered,0)) AS 'Total'
from (`order_records_future` join `products` on((`products`.`id` = `order_records_future`.`pr oduct_id`) ))
where (`order_records_future`.`r ound_id` >= 5) AND (`order_records_future`.`r ound_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.
SELECT products.pcode AS code, products.name as name,
sum(if((order_records_futu
sum(if((order_records_futu
sum(if((order_records_futu
OR (order_records_future.deli
from (`order_records_future` join `products` on((`products`.`id` = `order_records_future`.`pr
where (`order_records_future`.`r
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the points.
Glad I could help.
Mark.
Glad I could help.
Mark.
try:
SELECT products.pcode AS code, products.name as name,
sum(if((order_records_futu
sum(if((order_records_futu
sum(if((order_records_futu
OR (order_records_future.deli
from (`order_records_future` join `products` on((`products`.`id` = `order_records_future`.`pr
where (`order_records_future`.`r
group by products.name order by products.pcode
HAVING sum(if((order_records_futu
OR (order_records_future.deli
Hope this helps,
Mark.