Return non distict values
Posted: Tue Sep 25, 2007 5:44 am
Hi,
I have an ecommerce system, Virtuemart, that stores orders in 2 tables, jos_vm_orders and jos_vm_order_item.
Jos_vm_orders has a primary key, order_id and stores the price before discount, the coupon discount, and the final price.
Jos_vm_order_item has the foreign key order_id and stores the items that were contained in the order. It's primary key is order_item_id. It also stores an item price, but this does not include the coupon discount, as that is calculated on the total order and stored in the jos_vm_orders table.
Here's the problem:
The client would like to know how much revenue each indivual product has generated. This usually means going to the jos_vm_order_item table and then returning the sum of item_price GROUP BY product_id. Unfortunately this doesn't include the coupon discount, and is therefore inaccurate.
Thinking about the problem it is the non distinct records in the jos_vm_order_item table that cause the problem, as the coupon_discount stored in the jos_vm_order table relates to the total order, and this may have more than one item. I'm thinking that I would need to find the non-distinct values in jos_vm_order_item table, then compare the coupon_discount with the pre-discount price in the jos_vm_orders table to gain a percentage. This percentage can then be deducted from the jos_vm_order_item.item_price. I can then return the sum of these values GROUP BY product_id and add it to the sum of the distinct values GROUP BY product_id.
I have 2 questions:
Is there any easier way to do this?
If not, how can I return the non-distinct values from the jos_vm_order_item table?
Any help is greatly appreciated!
I have an ecommerce system, Virtuemart, that stores orders in 2 tables, jos_vm_orders and jos_vm_order_item.
Jos_vm_orders has a primary key, order_id and stores the price before discount, the coupon discount, and the final price.
Jos_vm_order_item has the foreign key order_id and stores the items that were contained in the order. It's primary key is order_item_id. It also stores an item price, but this does not include the coupon discount, as that is calculated on the total order and stored in the jos_vm_orders table.
Here's the problem:
The client would like to know how much revenue each indivual product has generated. This usually means going to the jos_vm_order_item table and then returning the sum of item_price GROUP BY product_id. Unfortunately this doesn't include the coupon discount, and is therefore inaccurate.
Thinking about the problem it is the non distinct records in the jos_vm_order_item table that cause the problem, as the coupon_discount stored in the jos_vm_order table relates to the total order, and this may have more than one item. I'm thinking that I would need to find the non-distinct values in jos_vm_order_item table, then compare the coupon_discount with the pre-discount price in the jos_vm_orders table to gain a percentage. This percentage can then be deducted from the jos_vm_order_item.item_price. I can then return the sum of these values GROUP BY product_id and add it to the sum of the distinct values GROUP BY product_id.
I have 2 questions:
Is there any easier way to do this?
If not, how can I return the non-distinct values from the jos_vm_order_item table?
Any help is greatly appreciated!