I have and Unknown column error in my JOIN altough the table alias is correct-CodePudding

So my query is this

SELECT 
        c.customer_id, 
        c.customer_lastname, 
        c.customer_firstname, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))  AS sold_products_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count, 
        ROUND(100 * SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
            / SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))) as returned_products_percent, 
        ci.customer_street_address, 
        ci.customer_phone
   
    FROM 
        `order_products` AS op 
        LEFT JOIN (
                SELECT order_id, MIN(o.order_datetime) as first_order_date, 
                MAX(o.order_datetime) as last_order_date, 
                DATEDIFF(NOW(), MAX(o.order_datetime)) as days_since_last_order, 
                COUNT(DISTINCT o.order_id) as orders_count, 
                SUM(o.order_total)  as orders_total
                FROM `order` as o
                GROUP BY order_id) as o2 ON o2.order_id = op.order_id
            LEFT JOIN customer AS c ON o.customer_id = c.customer_id  
            LEFT JOIN customer_address AS ci USING (customer_id) 
    GROUP BY 
        customer_id 
    ORDER BY 
        customer_id ASC limit 100

And I get an error - #1054 - Unknown column 'o.customer_id' in 'on clause' that I don't know how to solve. Is my Left join - select combination done correctly?

CodePudding user response：

You need to join order and order_products in the subquery where you're calculating the totals. And this needs to group by the customer ID. Then you join that with the customer table in the main query. None of the aggregate functions can be in the main query, because then they'll be multiplied by the number of addresses you're joining with.

You need a separate subquery that just aggregates from order without order_products, so that these aggregates won't be multiplied by the products.

You don't need any grouping in the main query, since there are now no aggregate functions there. You'll get a separate row of results for each customer address -- the order information will be the same, but the address will be different.

SELECT 
    c.customer_id, 
    c.customer_lastname, 
    c.customer_firstname, 
    o2.sold_products_count,
    o2.returned_products_count,
    ROUND(100 * returned_products_count / sold_products_count) AS returned_products_percent,
    ROUND(100 * SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
    / SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))) as returned_products_percent, 
    o1.orders_total,
    o1.orders_count,
    ci.customer_street_address, 
    ci.customer_phone,
    o1.first_order_date,
    o1.last_order_date,
    o1.days_since_last_order
FROM customer AS c
LEFT JOIN customer_address AS ci USING (customer_id) 
LEFT JOIN (
     SELECT
        o.customer_id,
        MIN(o.order_datetime) as first_order_date, 
        MAX(o.order_datetime) as last_order_date, 
        DATEDIFF(NOW(), MAX(o.order_datetime)) as days_since_last_order, 
        COUNT(*) as orders_count, 
        SUM(o.order_total)  as orders_total
    FROM `order` AS o
    GROUP BY o.customer_id) AS o1 ON o1.customer_id = c.customer_id
LEFT JOIN (
    SELECT
        o.customer_id,
        SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))  AS sold_products_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count
    FROM `order` as o
    JOIN order_products AS op ON o.order_id = op.order_id
    GROUP BY o.customer_id) as o2 ON o2.customer_id = c.customer_id
GROUP BY c.customer_id 
ORDER BY c.customer_id ASC
limit 100