Calculate column

Question

I want to calculate the column Rounds automatically, to get the last 10 games of the team understood? do not manage to do this with SELECT, I think it has to be in the creation of the table...

This is the table creation code and select:

create table Sweden (   
    TimeID varchar(40) not null,
    Rodada int,
    Mando varchar(7) not null,
    Feitos int not null,
    Sofridos int not null,
    Feitos1T int not null,
    Sofridos1T int not null,
    Feitos2T int as (Feitos - Feitos1T),
    Sofridos2T int as (Sofridos - Sofridos1T),
    Total int as(Feitos + Sofridos),    
    DadoInserridoEm date
);
select 
    TimeID as 'Time',
    sum(if(Rodada > 0,1,0)) as 'Rodadas',    
    round(avg(Feitos1T),2) as 'Feitos 1st' ,
    round(avg(Sofridos1T),2) as 'Sofridos 1st',    
    round(avg(Feitos2T),2) as 'Feitos 2nd' ,
    round(avg(Sofridos2T),2) as 'Sofridos 2nd',        
    round(avg(Feitos),2) as 'Feitos' ,
    round(avg(Sofridos),2) as 'Sofridos',    
    round(avg(Feitos1T) + avg(Sofridos1T),2) as 'Media 1st' ,   
    round(avg(Feitos2T) + avg(Sofridos2T),2) as 'Media 2nd' ,
    round(avg(Total),2) as 'Total',        
    SUM(if(FT80min > 0,1,0)) as 'ft80min',
    SUM(if(Total >= 9,1,0)) as 'Over 9 asi',
    SUM(if(Total >= 10,1,0)) as 'Over 10 asi',
    SUM(if(Feitos > Sofridos,1,0)) as '1x2', -- Teve mais cantos que o adversárioo
    max(DadoInserridoEm) as 'Inserido Em'
from Sweden
group by TimeID;

(I’m a beginner)

Answer 1

To resolve this query using sql, you must use the function limit together with the function order by.

This way your consultation would be:

SELECT 
    TimeID as 'Time',
    sum(if(Rodada > 0,1,0)) as 'Rodadas',    
    round(avg(Feitos1T),2) as 'Feitos 1st' ,
    round(avg(Sofridos1T),2) as 'Sofridos 1st',    
    round(avg(Feitos2T),2) as 'Feitos 2nd' ,
    round(avg(Sofridos2T),2) as 'Sofridos 2nd',        
    round(avg(Feitos),2) as 'Feitos' ,
    round(avg(Sofridos),2) as 'Sofridos',    
    round(avg(Feitos1T) + avg(Sofridos1T),2) as 'Media 1st' ,   
    round(avg(Feitos2T) + avg(Sofridos2T),2) as 'Media 2nd' ,
    round(avg(Total),2) as 'Total',        
    SUM(if(FT80min > 0,1,0)) as 'ft80min',
    SUM(if(Total >= 9,1,0)) as 'Over 9 asi',
    SUM(if(Total >= 10,1,0)) as 'Over 10 asi',
    SUM(if(Feitos > Sofridos,1,0)) as '1x2',
    DadoInserridoEm as 'Inserido Em'
FROM Sweden
GROUP BY TimeID
ORDER BY DadoInserridoEm DESC
LIMIT 10;

To learn more about how the limit and the order by see in the official documentation.