---

In my SQL, sometimes we need to calculate average or something else for filtered data. It woud be much easiler if we create temporary tables when calculating filtered data. here is an example.

First, let’s create a database

create database code_test;

Second, I’ll create a data table.

create table code_test.df (
    id int auto_increment primary key,
    treatment varchar(10),
    root_biomass float,
    total_biomass float
);

insert into code_test.df (treatment, root_biomass, total_biomass) VALUES
('N0', 4.429, 6.951),('N0', 2.848, 3.998),('N0', 5.627, 9.001),
('N0', 4.279, 5.983),('N0', 5.051, 7.004),('N1', 6.123, 9.876),
('N1', 4.765, 8.453),('N1', 5.908, 10.234),('N1', 3.987, 6.876),
('N1', 4.569, 7.983),('N2', 7.456, 12.345),('N2', 5.987, 10.654),
('N2', 6.234, 11.876),('N2', 4.876, 9.123),('N2', 5.765, 10.345),
('N3', 8.123, 14.234),('N3', 6.876, 12.876),('N3', 7.543, 13.654),
('N3', 5.987, 11.234),('N3', 6.654, 12.098);

Let’s see the data table was well created.

select * from code_test.df

Now, I want to calculate average for root and total biomass per treatment.

select treatment,
round(avg(root_biomass),2) as `Root Dry weight`,
round(avg(total_biomass),2) as 'Total Dry weight'
from code_test.df
group by treatment;

Next, I want to calculate average again, but excluding treatment, N3. So, I’ll run this code.

select treatment,
round(avg(root_biomass),2) as `Root Dry weight`,
round(avg(total_biomass),2) as 'Total Dry weight'
from code_test.df
where treatment!= "N3"
group by treatment;

It seems simple, but if you have more filtering or grouping, the code will be longer and longer. In this case, it would be better to create a new data table which was filtered by what I want.

# First, I'll designate the database I'll use 

use code_test;

# Second, I'll create a temporary data table named filtered_data from the data table; code_test.df (Remembr, I run 'use code_test', and I can create a temporary data table from the data table; df which is saved in the databse; code_test

create temporary table filtered_data as
select * from code_test.df
where treatment!= "N3";

Let’s see this new data table.

select * from filtered_data;

Now when I bring data, I did not designated database (i.e., select * from filtered_data.code_test) because this is temporary data table.

select treatment,
round(avg(root_biomass),2) as `Root Dry weight`,
round(avg(total_biomass),2) as 'Total Dry weight'
from filtered_data
group by treatment;

if you want to delete this temporary data table,

drop temporary table filtered_data;

full summary: https://github.com/agronomy4future/SQL/blob/main/temporary_tables

---

---