New column based on the sum of another column

Question

Hi Guys i want to create a column based on the sum of another column Let's say i have this table

Employee
id      iddepartment    idhostel     valuedepartment    
1       1               1                   15              
2       2               1                   15              
3       3               1                   15              
4       4               2                   10              
5       5               2                   10              
6       6               2                   10

I want to create another table with a calculated column (sum valuedepartment group by idhostel) that looks something like this one

Employee
id      iddepartment    idhostel     valuedepartment            valuehostel   
1       1               1                   15                      45
2       2               1                   15                      45 
3       3               1                   15                      45
4       4               2                   10                      30
5       5               2                   10                      30 
6       6               2                   10                      30

There is a way i can do this please ?

Possible duplicate of [Column calculated from another column?](https://stackoverflow.com/questions/5222044/column-calculated-from-another-column) — rsjaffe, Dec 29 '18 at 20:07
Hi thank you for your reply yes i checked your link but the problem is that we can perform some mathematics operations to store a column however if we want some agregate function like average or sum there is a problem — Houssam Zakaria Addad, Dec 29 '18 at 20:14
I'm guessing that value hostel is the sum of valudepartment by id hostel but I could be wrong, you didn't define. — P.Salmon, Dec 29 '18 at 20:17
Or value department = valuhostel / 3 or id hostel is a sequential number based on where value department and value hostel are the same — P.Salmon, Dec 29 '18 at 20:24

GMB · Answer 1 · 2018-12-29T21:17:44.050

0

You want to create a new column in your table, then update its content from other columns :

ALTER TABLE my_table ADD COLUMN my_sum INT;

UPDATE my_table SET my_sum = value1 + value2;

In your use case, the relevant query seems to be :

UPDATE my_table as t1
INNER JOIN (
    SELECT idhostel, SUM(valuedepartment) AS sum_dpt
    FROM my_table
    GROUP BY idhostel
) t2 on t2.idhostel = t1.idhostel
SET t1.valuehostel = t2.sum_dpt

edited Dec 29 '18 at 21:17

answered Dec 29 '18 at 20:15

GMB

216,147
25
84
135

Yess that's what i want but instead of having value1 and value2 as a columns i want them to be as rows update my_table set my_sum = sum(value1); something like that . – Houssam Zakaria Addad Dec 29 '18 at 20:19
@HoussamZakariaAddad : Mmm, not sure that it would make sense... Please update your question to show the expected results... – GMB Dec 29 '18 at 20:29
I really appreciate your help man !! )ld take i have a mysql table that already have these columns (id-iddepartment-idhostel -valuedepartment) and i want to create another column which is valuehoste that contains the sum of valuedepartment grouped by idhostel l ! sorry if i didn't make things clear from the begining – Houssam Zakaria Addad Dec 29 '18 at 20:50
Yess it looks something like that but the problem is that your solution updates also valuedepartment – Houssam Zakaria Addad Dec 30 '18 at 01:02

sticky bit · Answer 2 · 2018-12-29T20:32:50.107

0

The example suggests, that you want the sum of valuedepartment per idhostel. You can get that using a subquery.

SELECT e1.id,
       e1.iddepartment,
       e1.idhostel,
       e1.valuedepartment,
       (SELECT sum(e2.valuedepartment)
               FROM employee e2
               WHERE e2.idhostel = e1.idhostel) valuehostel
       FROM employee e1;

I don't recommend to persist this. You can always query it, like shown above, so you can get the information. Persisting values calculate from others bears the risk of inconsistencies however and should be avoided if possible. Otherwise great care has to be taken to avoid inconsistencies.

But you can use a view you can query like a table.

CREATE VIEW employees_with_valuehostel
AS
SELECT e1.id,
       e1.iddepartment,
       e1.idhostel,
       e1.valuedepartment,
       (SELECT sum(e2.valuedepartment)
               FROM employee e2
               WHERE e2.idhostel = e1.idhostel) valuehostel
       FROM employee e1;

You can then query the view.

SELECT *
       FROM employees_with_valuehostel;

would give you the same result as the query above.

edited Dec 29 '18 at 20:32

answered Dec 29 '18 at 20:19

sticky bit

36,626
12
31
42

This is the result i want thank you so much !! however it is possible to store the result in a seperate column ?? – Houssam Zakaria Addad Dec 29 '18 at 20:27
@HoussamZakariaAddad: As I wrote, I do not recommend to persist the vales. But you could use a view. See my edit. – sticky bit Dec 29 '18 at 20:33
I really appreciate your help man !! Your solution is what i wanted but the problem is that i am using this table in another program ( A spring boot program ) so the view is not the right path i should take i have a mysql table that already have these columns (id-iddepartment-idhostel -valuedepartment) and i want to create another column the other column which is valuehostel ! sorry if i didn't make things clear from the begining – Houssam Zakaria Addad Dec 29 '18 at 20:45

New column based on the sum of another column

2 Answers2