How do I change a function according to a value in a specific column

Question

Let's say I have a table with two columns:

1. Month
2. Value

I now want to create a 3rd column that will aggregate that value for each month (and will display the total product for each month in each row that is part of this month. Any general function that could do it without me having to specify each case in the function? (If I were to use an "If" function, that's what I would do..)

Example Given: My initial 2 columns are "Month" and "Value" and I want a function that will create the "Sum" comlumn.

Month  Value     Sum
6      23        57
6      34        57
7      56        100
7      44        100
8      12        12

Answer 1

General procedure

The general procedure would be to use sum with group by 'Month'. This will return a table where Month is used as index (no repeated months) and you have a column with the sums of all the months.

Using to your example, you will get the following intermediate table:

Month  Sum
6      57
7      100
8      12

Then you can use this table to create the Sum column in your original table.
Basically you just copy the value in Sum from the intermediate table in all the row with the same Month.

Now how to do this depends on the technology you use, which you have not specified, so I make a couple of examples with the tools I know: if you use different tools, try to adapt the idea.

Using python pandas

Suppose that df is your original table (without the Sum column) created with pandas. Then the procedure would be:

#create the intermediate table using sum and groupby
idf = df.groupby('Month').sum()

#edit idf so that the number of rows is the same of df
idf = idf.loc[df['Month']].reset_index()

#adding the column to the original table
df['Sum'] = idf['Value']

Using MySQL

Suppose that otable is your original table (without the Sum column) in a MySQL database. Then the procedure would be:

#create additional column to store the Sum, null by default 
ALTER TABLE otable ADD COLUMN Sum INT;

#create the intermediate table using sum and groupby
CREATE TEMPORARY TABLE idf SELECT SUM(Value) FROM otable GROUP BY Month;

#update the original table
UPDATE otable o, idf i SET o.Sum = i.`SUM(Value)` WHERE o.Month = i.Month;

Using excel

For excel it has been already answered here and here.