Calculation of growth percentage

Question

I have three columns that would be month, recipe and expenses. For example:

Mês       | Receita | Despesas 
Janeiro   | 2500    | 1000 
Fevereiro | 1000    | 500 

I would like to demonstrate the percentage of growth using PHP and Mysql.

The archive .sql is as:

CREATE TABLE `financas`   
  `codusuario` int (11)NOT NULL AUTO_INCREMENT,
  `mes` varchar(100) NOT NULL,    
  `receitas` varchar(100) NOT NULL,    
  `despesas` text(100) NOT NULL,    
  PRIMARY KEY (`codusuario`)   
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf

Answer 1

Solution to Visualize Monthly Growth:

Follow a query to get the columns and the growth rate monthly (assuming that each line was a month). For larger or total periods, simply adapt the solution to use SUM and GROUP BY or accumulate data in more @variables.

SET @anterior=0;

SELECT
      id,
      receitas,
      despesas,
      @subtotal := (receitas-despesas) AS subtotal,
      IF( @anterior !=0, (@subtotal/@anterior-1)*100, '?' ) AS crescimento,
      @anterior := @subtotal
   FROM rendimentos;

_{Line breaks are not necessary.}

Notice that it pays to do two darlings separate, to simplify, one with SET and one with SELECT.

The IF used to calculate growth does the following::

• if the previous value is non-zero, returns the growth rate in percentage.
• if the previous value is zero, returns ?. You can replace the ? by the value you find convenient for your application, such as 100, for example.
  Without this IF we would have trouble making a split by zero.

Values are being returned in percentage. To return the numeric index only, simply remove the *100 of the formula.

Click here for a functional example on SQL Fiddle

Based on your Pastebin PHP code, to show the result:

$query1 = "SET @anterior=0;";
$query2 = "SELECT id, receitas, despesas, @subtotal := (receitas-despesas) AS subtotal, IF( @anterior !=0, (@subtotal/@anterior-1)*100, '?' ) AS crescimento, @anterior := @subtotal FROM rendimentos;";

$conn = mysql_connect($host, $user, $pass) or die("Erro na conexão com o banco de dados");
$db = mysql_select_db($d_b, $conn) or die("Erro na seleção do banco de dados");

$result1 = mysql_query(query1);
$result2 = mysql_query(query2);
echo "<table>";
while ($resultRow = mysql_fetch_assoc($result2)) {
   echo '<tr><td>';
   echo htmlentities($resultRow['id']);
   echo '</td><td>';
   echo htmlentities($resultRow['receitas']);
   echo '</td><td>';
   echo htmlentities($resultRow['despesas']);
   echo '</td><td>';
   echo htmlentities($resultRow['subtotal']);
   echo '</td><td>';
   echo htmlentities($resultRow['crescimento']);
   echo '</td></tr>';
}
echo "</table>";

IMPORTANT: Avoid using functions mysql_, because they are insecure and are obsolete. The example provided was only to be able to meet what was requested at the time. I recommend you study the functions mysqli_ and convert your code.

Answer 2

If I understand the question, the formula for calculating this is:

x = past recipe - current recipe / past recipe

revenue = x * 100

In the link below, there are more complex examples.

http://pt.wikihow.com/Calcular-Taxa-de-Crescimento

Answer 3

SQL Fiddle link.

<?php
// Proposta utilizando PDO e cálculo do rendimento no PHP

function obterConexaoComOBancoDeDados ($bancoDeDados, $host, $usuario, $senha) {
    $dsn = "mysql:dbname=$bancoDeDados;host=$host";

    try {
        return new PDO($dsn, $usuario, $senha);
    } catch (PDOException $excecao) {
        echo "Falha ao tentar obter uma conexão com o Banco de Dados: {$excecao->getMessage()}";
    }
}

$dbh = obterConexaoComOBancoDeDados("hgsandbox", "localhost", "root", "");

$sql = '
    SELECT
        mes,
        (receita - despesa) AS liquido
    FROM
        Financas
    /* Seria necessário um WHERE?*/
    WHERE
        Usuario_id = :usuarioId
    ORDER BY
        mes
';

$usuarioId = 1; // Id de um usuário.

$sth = $dbh->prepare($sql);
$sth->bindParam(':usuarioId', $usuarioId);
$sth->execute();

$linhas = $sth->fetchAll(PDO::FETCH_ASSOC);

foreach ($linhas as $indice => $linha) {
    if ($indice > 0) {
        $mesAtual = (int) $linha["mes"];
        $valorLiquidoAtual = (float) $linha["liquido"];

        $porcentagemAlteracao = (($valorLiquidoAtual/$valorLiquidoAnterior) - 1) * 100;

        echo "A porcentagem de alteração (crescimento/redução) do mês $mesAnterior para o $mesAtual é de {$porcentagemAlteracao}%.";
    }

   $mesAnterior = (int) $linha["mes"];
   $valorLiquidoAnterior = (float) $linha["liquido"];
}