sql - MYSQL: View statement produces incorrect SUM totals -
i have 3 tables. table product prices, invoiced products, , ordered products. trying create view joins these. want output product prices total of invoiced products , total of ordered products.
products_price
id season_id product_id product_price 1 1 1 3.99 2 1 2 6.99 3 1 3 5.99 4 1 4 5.99 .... invoices_products
id season_id invoice_id product_id piece_qty 1 1 1 1 1600 2 1 2 2 3200 3 1 3 2 200 4 1 4 1 120 .... orders_products
id season_id order_id product_id piece_qty 1 1 1 1 160 2 1 2 1 40 3 1 2 2 20 4 1 3 2 10 .... here few queries view statements i've tried far.
this query gives me want. view's output perfect sum() first 2 rows off. total_invoice_product double row 1 , 2. total_order_productis 4x row 1 , 3x row 2.
statement 1:
select `t1`.`id` `id`, `t1`.`season_id` `season_id`, `t1`.`product_id` `product_id`, `t1`.`product_piece_price` `product_piece_price`, sum(`t2`.`piece_qty`) `total_invoice_product`, sum(`t3`.`piece_qty`) `total_order_product` ((`products_price` `t1` left join `invoices_products` `t2` on (((`t2`.`product_id` = `t1`.`product_id`) , (`t2`.`season_id` = `t1`.`season_id`)))) left join `orders_products` `t3` on (((`t3`.`product_id` = `t1`.`product_id`) , (`t3`.`season_id` = `t1`.`season_id`)))) group `t1`.`season_id` , `t1`.`product_id` this query gives me output expect. not full output want correct statement. sum() totals off on 1 well.
statement 2:
select `t1`.`id` `id`, `t1`.`season_id` `season_id`, `t1`.`product_id` `product_id`, `t1`.`product_price` `product_price`, sum(`t2`.`piece_qty`) `total_invoice_product`, sum(`t3`.`piece_qty`) `total_order_product` ((`products_price` `t1` left join `invoices_products` `t2` on ((`t2`.`product_id` = `t1`.`product_id`))) left join `orders_products` `t3` on ((`t3`.`product_id` = `t1`.`product_id`))) ((`t2`.`season_id` = `t1`.`season_id`) , (`t2`.`product_id` = `t1`.`product_id`)) group `t1`.`season_id` , `t1`.`product_id` the output want
id season_id product_id product_price total_invoice total_order 1 1 1 3.99 1720 200 2 1 2 6.99 3400 30 3 1 3 5.99 576 4 1 4 5.99 800 output received statement 1
id season_id product_id product_price total_invoice total_order 1 1 1 3.99 3440 800 2 1 2 6.99 6800 90 3 1 3 5.99 576 4 1 4 5.99 800 output received statement 2
id season_id product_id product_price total_invoice total_order 1 1 1 3.99 3440 800 2 1 2 6.99 6800 90 i can build query below , works perfect. exact output need code not work view. error: error 1349: view's select contains subquery in clause sql statement
perfect query not work view
select products_price.id, products_price.season_id, products_price.product_id, products_price.product_price, invoices_grouped.total_invoice_product, orders_grouped.total_order_product products_price left join (select invoices_products.product_id, invoices_products.season_id, sum(invoices_products.piece_qty) total_invoice_product invoices_products group invoices_products.product_id) invoices_grouped on invoices_grouped.product_id = products_price.product_id , invoices_grouped.season_id = products_price.season_id left join (select orders_products.product_id, orders_products.season_id, sum(orders_products.piece_qty) total_order_product orders_products group orders_products.product_id) orders_grouped on orders_grouped.product_id = products_price.product_id , orders_grouped.season_id = products_price.season_id what need
i've tried several other statements. either got worse results or same. can me statement 1 working proper sum?
edit 1 question
the information view provides called upon lot. products_price , invcoices_products tables not changed often. orders_products changed lot. if 2 views required, more efficient use "perfect" query above or use 2 views?
edit 2 query
here query view statement. query part of statement 1 shown above. query works perfect not complete. need second sum column. when add second left join breaks sum totals.
select `t1`.`id` `id`, `t1`.`season_id` `season_id`, `t1`.`product_id` `product_id`, `t1`.`product_piece_price` `product_piece_price`, sum(`t2`.`piece_qty`) `total_invoice_product` (`products_price` `t1` left join `invoices_products` `t2` on (((`t2`.`product_id` = `t1`.`product_id`) , (`t2`.`season_id` = `t1`.`season_id`)))) group `t1`.`season_id` , `t1`.`product_id` output
id season_id product_id product_price total_invoice 1 1 1 3.99 1720 2 1 2 6.99 3400 3 1 3 5.99 576 4 1 4 5.99 800
well, mysql has limitations, need create 2 views subqueries , use them:
create view viewinvoices select season_id, product_id, sum(piece_qty) pq invoices_products group season_id, product_id create view vieworders select season_id, product_id, sum(piece_qty) pq orders_products group season_id, product_id select pp.id, pp.season_id, pp.product_id, pp.product_price, i.pq total_invoice, o.pq total_order products_price pp left join viewinvoices on pp.season_id = i.season_id , pp.product_id = i.product_id left join vieworders o on pp.season_id = o.season_id , pp.product_id = o.product_id
Comments
Post a Comment