MYSQL – Sum from multiple columns in one table divided by one row in a JOINED other table

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

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