□ 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.
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">CREATE DATABASE</mark> test_data;
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">CREATE TABLE</mark> test_data.crop_yield (
ID <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">INT AUTO_INCREMENT PRIMARY KEY</mark>,
Reps <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">INT</mark>,
Variable <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">VARCHAR</mark>(15),
Yield <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">DECIMAL</mark>(5, 2)
);
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">INSERT INTO</mark> test_data.crop_yield (Reps, Variable, Yield) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">VALUES</mark>
(1, 'Control', 116.08),
(2, 'Control', 132.85),
(3, 'Control', 115.98);
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">INSERT INTO</mark> test_data.crop_yield (Reps, Variable, Yield) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">VALUES</mark>
(1, 'Treatment', 131.37),
(2, 'Treatment', 108.89),
(3, 'Treatment', 100.35);
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> 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 |
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> *, (Yield-20) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">AS</mark> Yield_corrected <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> 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,
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ALTER TABLE</mark> test_data.crop_yield <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">DROP COLUMN</mark> Yield_corrected;
and then I’ll create a new ‘Yield_corrected’ column.
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ALTER TABLE</mark> test_data.crop_yield <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ADD COLUMN</mark> Yield_corrected <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">DECIMAL</mark>(10, 2); <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> test_data.crop_yield;
Then, I’ll recalculate the yield.
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">UPDATE</mark> test_data.crop_yield
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SET</mark> Yield_corrected = <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">CASE</mark>
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHEN</mark> Variable='Control' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">THEN</mark> Yield-20
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHEN</mark> Variable='Treatment' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">THEN</mark> Yield+10
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ELSE</mark> Yield
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">END</mark>;
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> 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.
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ALTER TABLE</mark> test_data.crop_yield
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ADD COLUMN</mark> Yield_moisture <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">DECIMAL</mark>(10, 2);
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">UPDATE</mark> test_data.crop_yield
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SET</mark> Yield_moisture= <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">CASE</mark>
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHEN</mark> Variable='Control' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">THEN</mark> Yield_corrected-Yield_corrected*0.15
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">WHEN</mark> Variable='Treatment' <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">THEN</mark> Yield_corrected-Yield_corrected*0.15
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">ELSE</mark> Yield_corrected
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">END</mark>;
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">SELECT</mark> * <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">FROM</mark> test_data.crop_yield;
#The full code: <a href="https://github.com/agronomy4future/SQL/blob/main/new_column_calculcated" target="_blank" rel="noreferrer noopener">https://github.com/agronomy4future/SQL/blob/main/new_column_calculcated </a>
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;