CODEWITHSUNDEEP
mysqlsql
Sam
Sam

Reputation: 10113

MySQL: Value of child lines based on proportion of cost?

We have a packslip lines table with the following structure (simplified):

A packslip line can represent an individual product, a parent kit, or kit components. For this exercise, use the following data set:

1, 'ITEM1', 'Item # 1', 0.3, 9.99, 4.79, 0, null
2, 'KIT1', 'Kit # 1', 1.3, 29.99, 0, 1, null
3, 'KITITEM1', 'Kit Item # 1', 0.7, 0, 10.0, 0, 2
4, 'KITITEM2', 'Kit Item # 2', 0.3, 0, 2.49, 0, 2
5, 'KITITEM3', 'Kit Item # 3', 0.3, 0, 4.29, 0, 2

As you can hopefully see, ITEM1 is a regular/individual product, KIT1 is a parent kit, and the last 3 items are child components for KIT1.

Notice that the kit lacks a cost and that the kit items lack a value. I need to create a query that will calculate the kit item values based on the proportion of the items' costs to the overall cost of the kit.

So in this example:

KITITEM1 Value = 10 / (10.0 + 2.49 + 4.29) * 29.99 = $17.87

KITITEM2 Value = 2.49 / (10.0 + 2.49 + 4.29) * 29.99 = $4.45

KITITEM3 Value = 4.29 / (10.0 + 2.49 + 4.29) * 29.99 = $7.67

Can I accomplish this in a single query (can have nested queries)? How?

Upvotes: 0

Views: 37

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

try this query, sqlFiddle

SELECT T1.line_id,
       T1.sku,
       T1.name,
       T1.weight,
       IF (T1.parent_line_id IS NULL,T1.value,
           ROUND(T1.cost * T2.value_divided_by_total_cost,2))as value,
       T1.cost,
       T1.is_kit,
       T1.parent_line_id
FROM packslip T1
LEFT JOIN
   (SELECT parent_line_id,(SELECT value FROM
           packslip p2 WHERE p1.parent_line_id = p2.line_id)
           /SUM(cost)
           as value_divided_by_total_cost
    FROM packslip p1
    WHERE parent_line_id IS NOT NULL
    GROUP BY parent_line_id
    )T2
ON T1.parent_line_id = T2.parent_line_id

The query LEFT JOIN with a derived table that gets the (value of parent) divided by SUM(cost) GROUP BY that parent.
Then the outer query checks to see if parent_line_id IS NOT NULL and multiplies the cost by value from the derived table.

Upvotes: 1

Related Questions