Question 215592 in Mysql Question posted in
The official MySQL documentation can be found here.

Do calculation in join query of MySQL

I have some calculation and want to do this in the query.

There is parent and children tables with one-to-many relationship:

CREATE TABLE `parent` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`)
);

CREATE TABLE `children` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NOT NULL,
  `multiple` decimal(10,2) DEFAULT NULL,
  `sum` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
);

For find the final value of parent I should iterate in children and calculate following formula:
newParentValue = childMultiple(parentValue + childSum)

The implementation in the code is as follows:

function calculateFinalParentValue($parentValue, $children)
{
    foreach ($children as $child) {
        $parentValue = $child['multiple'] * ($parentValue + $child['sum']);
    }
    return $parentValue;
}

How can I implement the calculation in the query?

I try this way (Using temporary variable):

set @value = 0; 

SELECT 
    p.id,
    @value := (c.multiple * (@value + c.sum)) AS value
FROM
    parent p
JOIN
    children c ON p.id = c.parent_id AND @value := p.value;

I set the variable in the join condition (@value := p.value) to reset the variable for each new parent.

This query returns rows for each parent with the number of children, and I need the last row in join for each parent as the answer.

But this way is not sustainable, Is there a better way?

Example:

mysql> select * from parent;
+----+-------+
| id | value |
+----+-------+
|  1 | 10.00 |
|  2 | 20.00 |
+----+-------+

mysql> select * from children;
+----+-----------+----------+------+
| id | parent_id | multiple | sum  |
+----+-----------+----------+------+
|  1 |         1 |     1.00 | 1.00 |
|  2 |         1 |     1.00 | 1.00 |
|  3 |         1 |     1.00 | 1.00 |
|  4 |         2 |     2.00 | 2.00 |
|  5 |         2 |     2.00 | 2.00 |
+----+-----------+----------+------+

With the above data, I expect following answer:

+----+--------+
| id | value  |
+----+--------+
|  1 |  11.00 |
|  1 |  12.00 |
|  1 |  13.00 | <- final value for parant.id = 1
|  2 |  44.00 |
|  2 |  92.00 | <- final value for parant.id = 2
+----+--------+

For parent.id=1 there is three children and parent.value is 10, so after calculate formula for first child new value is 1 * (10 + 1) = 11 and after second child value is 1 * (11 + 1) = 12 as expected after third child value is 1 * (12 + 1) = 13 (In all three children multiple and sum is equal to 1).

For parent.id=2 there is two children and parent.value is 20, so after calculate formula for first child new value is 2 * (20 + 2) = 44 and after second child value is 2 * (44 + 2) = 92 (In both two children multiple and sum is equal to 2).

And finally I want only final value for each parent so my final expected result is:

+----+--------+
| id | value  |
+----+--------+
|  1 |  13.00 |
|  2 |  92.00 |
+----+--------+
Tags:

4

Answers


  1. 0

    In SQL this would look like:

    SELECT
    `parent`.`id`,
    `children`.`multiple` * ( `parent`.`value` + `children`.`sum` ) as CalculatedValue
FROM `parent`
INNER JOIN `children` ON `parent`.`id` = `children`.`parent_id`
    Login or Signup to reply.
  2. 0

    To get the expected output, we need first identify the last child per parent and then join it with both the parent and children tables:

    SELECT p.id, c.multiple * ( p.value + c.sum )
FROM parent p
INNER JOIN (
  select parent_id, max(id) as last_row
  from children
  group by parent_id
) as s on s.parent_id = p.id
inner join children c on c.id = s.last_row
    Login or Signup to reply.
  3. 0

    A little tricky as you have to reset your value in between when the parents get changed.

    Try the following query:

    SELECT 
parentId,
ROUND(iteratingValue, 2) reqValue
 FROM 
        (SELECT 
        parentId,
        `childMultiple`,
        childSum,
        @running_parent,
        (CASE WHEN @current_parent_value=0 THEN @current_parent_value:=parentValue ELSE @current_parent_value=@current_parent_value END) ,
        (CASE WHEN @running_parent!=parentId   THEN @current_parent_value:=parentValue ELSE @current_parent_value:=@current_parent_value END),
        @current_parent_value:=(`childMultiple`*(@current_parent_value+childSum)) AS iteratingValue,
        @running_parent:=parentId
        FROM (SELECT 
        p.`id` parentId,
        c.`multiple`childMultiple,
        p.`value` parentValue,
        c.`sum` AS childSum,
        @current_parent_value:=0,
        @running_parent:=0
        FROM parent p
        JOIN `children` c ON c.`parent_id`=p.`id`
) subTable ORDER BY parentId) finalTable;

    You can also replace above mentioned CASE statements with IF(a little bit more readable)

    IF(@current_parent_value=0, @current_parent_value:=parentValue, @current_parent_value=@current_parent_value),
IF(@running_parent!=parentId, @current_parent_value:=parentValue, @current_parent_value:=@current_parent_value),

    It should give you your desired output.

    I have used two variables @current_parent_value and @running_parent

    The @running_parent will help you determine whether the previous row and current row belong to same parent and the @current_parent_value will help you store your current running value.

    Login or Signup to reply.
  4. 0

    Use ROW_NUMBER() window function to rank the rows of children partitioned by parent_id and ordered by id and SUM() window function to get the sum that you want:

    WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) rn FROM children) 
SELECT c.id,
       POW(c.multiple, c.rn) * p.value + SUM(POW(c.multiple, c.rn)) OVER (PARTITION BY p.id ORDER BY c.rn) * c.sum value
FROM parent p INNER JOIN cte c
ON c.parent_id = p.id;

    See the demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search