Help with MySQL query - Product orders report without duplicate shipping charges
- by Paul
Hello, I have an issue creating a custom report for an e-commerce store running on osCommerce. The client wants the report to have the following columns:
Date, Order ID, Product Class, Product Price, Product Tax, Shipping, Order Total
The criteria for generating the report are Date Range and Product Class (Textbooks for example)
The client wants the report to list each Textbook purchased on its own line. Orders with multiple textbooks would display a separate line for each Textbook in the order.
I have it all working except for one part: the shipping amount is order-specific (based on the order total), not product-specific, and is displaying for each product. I need it to display only for the first product of each order, so it is not counted more than once.
My current query is:
SELECT op.date_funds_captured as 'Date', op.orders_id as 'Order ID', pc.class as 'Product Class', round(op.products_price,2) as 'Product Price', round(op.products_tax*op.products_price/100,2) as 'Product Tax', round(otship.value,2) as 'Shipping', round(ot.value,2) as 'Order Total' from orders_products op, orders_total ot, orders_total otship, productclasses pc, products p where ot.orders_id = op.orders_id and ot.class='ot_total' and op.orders_id = otship.orders_id and otship.class = 'ot_shipping' and p.products_class_id = pc.id and op.products_id = p.products_id and pc.id = 1
pc.id = 1 -- Product class = Textbook
Here is an example of the current report output. You can see the problem with order 2256 showing the shipping value three times instead of once:
Date Order Product Class Price Tax Shipping Total
2010-01-04 2253 Textbook 24.95 2.43 10.03 37.41
2010-01-04 2256 Textbook 34.95 0.00 18.09 240.37
2010-01-04 2256 Textbook 55.50 0.00 18.09 240.37
2010-01-04 2256 Textbook 36.95 0.00 18.09 240.37
2010-01-04 2258 Textbook 55.50 5.41 12.17 124.24
Please help!!!
Thanks,
Paul
