Do calculation in join query of MySQL

Question

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 |
+----+--------+

Just in this case to simplify the example, all multiply and sum column of children table for each parent are equal (assume different values) and the final value is the maximum, the final value may not be the maximum every time.

Asgar · Accepted Answer · 2023-07-25T14:06:03.263

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.

Problem solved, now what is the best way to achieve the final result (I update the question)? — Amirhossein Aliakbarpour, Jul 25 '23 at 14:27
Then you should be using Windows Functions. See @Forpa's answer — Asgar, Jul 25 '23 at 15:00

forpas · Answer 2 · 2023-07-25T15:02:35.690

1

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 sums that you want.
Finally use FIRST_VALUE() window function to get the last sum of each id:

WITH 
  cte_children AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) rn FROM children), 
  cte_sums AS (
    SELECT p.id,
           c.rn,
           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_children c
    ON c.parent_id = p.id
  )
SELECT DISTINCT id, 
       FIRST_VALUE(value) OVER (PARTITION BY id ORDER BY rn DESC) value
FROM cte_sums;

See the demo.

edited Jul 25 '23 at 15:02

answered Jul 25 '23 at 14:37

forpas

160,666
10
38
76

Just in this case to simplify the example, all multiply and sum column of children table for each parent are equal, but in reality these values are completely different from each other. @Asgar's answer is currect for different values but using temporary variable, **Is there a way to solve the problem using window functions?** – Amirhossein Aliakbarpour Jul 26 '23 at 09:38
@AmirhosseinAliakbarpour my code is about the question as you posted it. If your requirement is different you should post a new question with the actual data that you have and your expected results. Then we can see if there is a solution with window functions. – forpas Jul 26 '23 at 10:21

Luuk · Answer 3 · 2023-07-25T12:52:50.497

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`

edited Jul 25 '23 at 12:52

answered Jul 25 '23 at 12:06

Luuk

12,245
5
22
33

It gives incorrect answer, I need to iterate and the value changes in each iteration! – Amirhossein Aliakbarpour Jul 25 '23 at 12:11
Please improve the question by showing some input data, and expected (=desired) output data. – Luuk Jul 25 '23 at 12:12
The question has been updated, Thanks. – Amirhossein Aliakbarpour Jul 25 '23 at 13:13
So, basically, your question is a duplicate of [Create a Cumulative Sum Column in MySQL](https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) (with a small modification) – Luuk Jul 25 '23 at 13:24

score 0 · Answer 4 · answered Jul 25 '23 at 12:49

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

answered Jul 25 '23 at 12:49

SelVazi

10,028
2
13
29

All children should affect on value, but in this query only last one is affecting! – Amirhossein Aliakbarpour Jul 25 '23 at 13:14

Do calculation in join query of MySQL

4 Answers4