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!
Return non distict values
Moderator: General Moderators
If the order discount applies to everything in the order, can't you do something like this?
Code: Select all
SELECT SUM(o.discount * oi.price * oi.quantity), oi.product_id
FROM jos_vm_orders o
LEFT JOIN jos_vm_order_item oi ON o.order_id = oi.order_id
GROUP BY oi.product_id
I'm not sure I can do it all in one query as the coupon_discount is generally applied as a percentage of the total order. To break the discount down for the orders individual items would involve finding what that percentage was (as there are different discounts and the one that the order was based upon is not stored in the db). I can then use the percentage to discount the individual item.
It sounds like you need to rethink your database design.
In the order you should store the order discount, then for each item you should store the price. There is no reason to store the order total since it can be easily calculated by adding up the cost of the individual items and subtracting the order discount. This would solve a lot of problems and make the problem you are currently trying to solve a lot easier.
However, it is possible to do this if the discount percentage is not stored in the order, you can determine that percentage like this
The discount divided by the order price will give you the amount that the order was discounted
This is assuming that it is stored like this:
Total order price: $80
Discount percentage: 25%
Discount: $20
For the above coupon_discount would be stored as 20 and pre_discount_price would be stored as $80. You would not need to do any distinct stuff because for the orders with a discount of 0 it will still calculate out right. Keep in mind however that there might be some rounding issues with this, so make sure and test it and get the rounding correct otherwise you may be off by pennies for every item which could add up to a considerable amount.
In the order you should store the order discount, then for each item you should store the price. There is no reason to store the order total since it can be easily calculated by adding up the cost of the individual items and subtracting the order discount. This would solve a lot of problems and make the problem you are currently trying to solve a lot easier.
However, it is possible to do this if the discount percentage is not stored in the order, you can determine that percentage like this
Code: Select all
SELECT SUM((oi.price - ((o.coupon_discount / o.pre_discount_price) * oi.price)) * oi.quantity), oi.product_id
FROM jos_vm_orders o
LEFT JOIN jos_vm_order_item oi ON o.order_id = oi.order_id
GROUP BY oi.product_id
This is assuming that it is stored like this:
Total order price: $80
Discount percentage: 25%
Discount: $20
For the above coupon_discount would be stored as 20 and pre_discount_price would be stored as $80. You would not need to do any distinct stuff because for the orders with a discount of 0 it will still calculate out right. Keep in mind however that there might be some rounding issues with this, so make sure and test it and get the rounding correct otherwise you may be off by pennies for every item which could add up to a considerable amount.
Thanks Begby, that's exactly what I was looking for (it was a long time ago that I got my Maths O Level!).
I agree about needing a database redesign, unfortunately it's part of an off the shelf ecommerce system that gets complicated when you start to pull apart it's classes, and they are tied up with the Joomla classes that it is plugged into.
I agree about needing a database redesign, unfortunately it's part of an off the shelf ecommerce system that gets complicated when you start to pull apart it's classes, and they are tied up with the Joomla classes that it is plugged into.