---

□ Creating Data Tables in MySQL: A Step-by-Step Guide

---

If you followed my previous post, you can manually create a Data Table. If you copy and paste the code below into your MySQL window and then run it with Ctrl+Shift+Enter, you will see the below data table.

CREATE DATABASE test_data;
CREATE TABLE test_data.crop_yield (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Reps INT,
    Variable VARCHAR(15),
    Yield DECIMAL(5, 2)
);

INSERT INTO test_data.crop_yield (Reps, Variable, Yield) VALUES
    (1, 'Control', 116.08),
    (2, 'Control', 132.85),
    (3, 'Control', 115.98);

INSERT INTO test_data.crop_yield (Reps, Variable, Yield) VALUES
    (1, 'Treatment', 131.37),
    (2, 'Treatment', 108.89),
    (3, 'Treatment', 100.35);
    
SELECT * FROM test_data.crop_yield;

---

■ How to calculate new values?

Now, I’d like to calculate new values. For example, I’ve discovered that the yield data was overestimated, and I want to subtract 20 from each value.

ID	Reps	Variable	Yield	Yield_corrected
1	1	Control	116.08	96.08
2	2	Control	132.85	112.85
3	3	Control	115.98	95.98
4	1	Treatment	131.37	111.37
5	2	Treatment	108.89	88.89
6	3	Treatment	100.35	80.35

---

SELECT *, (Yield-20) AS Yield_corrected FROM test_data.crop_yield

---

■ How to calculate new values according to different variables?

Now, let’s take another step. I discovered that the yield data was overestimated in the Control group but underestimated in the Treatment group. I’ll subtract 20 in the Control group and add 10 in the Treatment group.

ID	Reps	Variable	Yield	Yield_corrected
1	1	Control	116.08	96.08
2	2	Control	132.85	112.85
3	3	Control	115.98	95.98
4	1	Treatment	131.37	141.37
5	2	Treatment	108.89	118.89
6	3	Treatment	100.35	110.35

First, I’ll delete the ‘Yield_corrected’ column I created,

ALTER TABLE test_data.crop_yield
DROP COLUMN Yield_corrected;

and then I’ll create a new ‘Yield_corrected’ column.

ALTER TABLE test_data.crop_yield 
ADD COLUMN Yield_corrected DECIMAL(10, 2);

SELECT * FROM test_data.crop_yield;

Then, I’ll recalculate the yield.

UPDATE test_data.crop_yield
SET Yield_corrected = CASE
    WHEN Variable='Control' THEN Yield-20
    WHEN Variable='Treatment' THEN Yield+10
    ELSE Yield
END;

SELECT * FROM test_data.crop_yield;

Additionally, I’ve determined that moisture content is significant, and I’ve adjusted it to 15% to calculate the final corrected yield.

ID	Reps	Variable	Yield	Yield_corrected	Yield_moisture
1	1	Control	116.08	96.08	81.67
2	2	Control	132.85	112.85	95.92
3	3	Control	115.98	95.98	81.58
4	1	Treatment	131.37	141.37	120.16
5	2	Treatment	108.89	118.89	101.06
6	3	Treatment	100.35	110.35	93.80

So, I’ll create a new column named ‘Yield_moisture’ and adjust the yield for a 15% moisture.

ALTER TABLE test_data.crop_yield 
ADD COLUMN Yield_moisture DECIMAL(10, 2);

UPDATE test_data.crop_yield
SET Yield_moisture= CASE
    WHEN Variable='Control' THEN Yield_corrected-Yield_corrected*0.15
    WHEN Variable='Treatment' THEN Yield_corrected-Yield_corrected*0.15
    ELSE Yield_corrected
END;

SELECT * FROM test_data.crop_yield;

---

#The full code: https://github.com/agronomy4future/SQL/blob/main/new_column_calculcated 

CREATE DATABASE test_data;
CREATE TABLE test_data.crop_yield (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Reps INT,
    Variable VARCHAR(15),
    Yield DECIMAL(5, 2)
);

INSERT INTO test_data.crop_yield (Reps, Variable, Yield) VALUES
    (1, 'Control', 116.08),
    (2, 'Control', 132.85),
    (3, 'Control', 115.98);

INSERT INTO test_data.crop_yield (Reps, Variable, Yield) VALUES
    (1, 'Treatment', 131.37),
    (2, 'Treatment', 108.89),
    (3, 'Treatment', 100.35);
    
ALTER TABLE test_data.crop_yield 
ADD COLUMN Yield_corrected DECIMAL(10, 2);

UPDATE test_data.crop_yield
SET Yield_corrected = CASE
    WHEN Variable='Control' THEN Yield-20
    WHEN Variable='Treatment' THEN Yield+10
    ELSE Yield
END;

ALTER TABLE test_data.crop_yield 
ADD COLUMN Yield_moisture DECIMAL(10, 2);

UPDATE test_data.crop_yield
SET Yield_moisture= CASE
    WHEN Variable='Control' THEN Yield_corrected-Yield_corrected*0.15
    WHEN Variable='Treatment' THEN Yield_corrected-Yield_corrected*0.15
    ELSE Yield_corrected
END;

SELECT * FROM test_data.crop_yield;

---