variacion porcentual entre dos columnas

Question

Hello, I made a query to separate the number of roles per year, leaving 3 columns: Commune, initial (number of roles year 2011), final (number of roles year 2021) and now I want to make the variation (final-initial)/final, But it's not working.

Use this code to separate the 3 columns
select c.comuna, count(t.numero_linea) inicial,
        (select count(t.numero_linea)
        from const t
        where t.periodo=('1-2021') and t.cod_com=c.cod_com) final
    
from codigo_comuna_region as c, const t
where (t.periodo=('1-2011') and t.cod_com=c.cod_com)

group by c.comuna, c.cod_com

And use this code to make the variation but it doesn't work for me:
select ((final-inicial)/final) variacion,c.comuna,
    (select c.comuna, count(t.numero_linea) inicial,
        (select count(t.numero_linea)
        from const t
        where t.periodo=('1-2021') and t.cod_com=c.cod_com) final
    
    from codigo_comuna_region as c, const t
    where (t.periodo=('1-2011') and t.cod_com=c.cod_com))
from codigo_comuna_region as c, const t

group by c.comuna, c.cod_com
order by variacion desc limit 10

Answer

I hope this is helpful for you.

```
SELECT 
  (final - inicial) / final AS variacion, 
  c.comuna, 
  inicial, 
  final
FROM 
  codigo_comuna_region AS c 
  INNER JOIN (
    SELECT 
      cod_com, 
      count(numero_linea) AS inicial, 
      (
        SELECT 
          count(numero_linea) 
        FROM 
          const 
        WHERE 
          periodo = '1-2021' AND 
          cod_com = c1.cod_com
      ) AS final
    FROM 
      const AS c1 
    WHERE 
      periodo = '1-2011' 
    GROUP BY 
      cod_com
  ) AS d ON c.cod_com = d.cod_com 
ORDER BY 
  variacion DESC 
LIMIT 
  10;

```

variacion porcentual entre dos columnas

Relevant content