I have 2 tables for my online orders: product_items & order
The product_item contains selling_price, qty and cost_price the order table includes cost_shipping and currency
I want to get a total of all the selling_price (* qty) and cost_price (* qty) and the cost_shipping for all orders (grouped by currency)
The problem is that when I sum the cost_shipping it adds up one cost_shipping for each item in the order (because they are JOINED)
So if order 1 had 2 items and a cost_shipping of €10 then it would return €20.
If I don’t SUM the cost_shipping then I just get the last value
SELECT currency, SUM(`cost_price` * qty), SUM(`selling_price` * qty), SUM(cost_shipping) FROM `product_items` INNER JOIN `orders` ON `3xm_orders_new`.id = `3xm_item_sales_new`.order_id WHERE `date` >= '2020-02-01' AND `date` < '2020-03-01' GROUP BY currency
I need these values to workout the margin on the order.
Any suggestions as to how I can achieve this?
Thanks in advance
TempPeck
Advertisement
Answer
You would need to aggregate the costs and prices per order in a subquery first, then join it with the orders, and aggregate by currency:
SELECT o.currency, SUM(p.cost_price) sum_cost_price, SUM(p.selling_price) sum_selling_price, SUM(cost_shipping) sum_cost_shipping FROM ( SELECT order_id, SUM(cost_price * qty) cost_price, SUM(selling_price * qty) selling_price, FROM product_items GROUP BY order_id ) p INNER JOIN orders o ON o.id = p.order_id WHERE o.date >= '2020-02-01' AND o.date < '2020-03-01' GROUP BY o.currency
Side notes:
table aliases make the query easier to write, read and maintain; I modified your code to use them
always qualify each column in your queries with the table it belongs to; this makes it much easier to follow the logic; I added qualifiers for every column (I had to make a few assumptions, namely for the
date
column)you should generally avoid surrounding every table and column name with backticks; they are needed only when the identifiers contain special characters, which is not the case here
there are discrepencies on the table names in your original query; I used those you described in the question