Perform direct calculations in the sql query

Question

How could you perform a series of calculations directly in the sql query, as there is SUM() that performs the sum would have some way to add one column to another and decrease another?

$sql02 = mysql_query("SELECT * , SUM(valor) as tt FROM finan  "); 
$dados02 = mysql_fetch_array($sql02); 

$fev_c  = $dados02[$pg];
$fev_v  = $dados02[$pg];
$tot =  $dados02['tt'];
$juros  = $dados02['multa'];
$multa  = $dados02['juros'];
$desconto  = $dados02['desconto'];

// abaixo é o calculo que atualmente fasso é e isso que queria fazer direto na consulta
$fev2 = $tot+$juros+$multa-$desconto;

my question is it would have as this calculation already be made in the query

SELECT *, SUM(valor) as tt FROM finan;

Answer 1

No problem doing the operations in SQL

SELECT
   SUM(valor)    AS totalvalor,
   SUM(juros)    AS totaljuros,
   SUM(multa)    AS totalmulta,
   SUM(desconto) AS totaldesconto,
   SUM(valor + juros + multa - desconto) AS totalgeral
FROM
   financeiro

The important thing is that you have one alias for each expression to facilitate use in client-language.

More than that: in other scenarios you can do conditional operations and effectively "create" new columns in return based on this:

SELECT
   SUM(IF(operacao='d',valor,0)) AS debito,
   SUM(IF(operacao='c',valor,0)) AS credito
FROM
   tabela

Again, worth the idea of a alias for every expression.

As noted by the colleague @Motta, if there is any null value in the fields, the total result is undone. If this is your case, you can reset the null values in this way:

Instead of using:

SUM( coluna1 + coluna2 ...

Give preference to:

SUM( COALESCE( coluna1, 0 ) + COALESCE( coluna2, 0 ) ...

The COALESCE makes a cascade of values in the list of parameters, until they end up or a nonzero is found. In our case, since we only use two, the first one is returned, unless it is null, when the 0 in his stead.