Perform direct calculations in sql query

Question

Perform direct calculations in sql query

Navigation

#1 by (5 votes)

2

How could I perform a series of calculations directly in the sql query, how does SUM() that sum does it have some way of adding one column to another and decreasing 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 how would this calculation be done in the query

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

mysql sql

asked by anonymous 30.01.2017 / 23:57

1 answer

PHP - List the files in a directory [duplicate] Error while trying to read excel file C #

score 5 · Accepted Answer

No problem doing SQL operations

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

What's important is that you have alias for each expression to make it easier to use in the 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, the idea of an alias for each expression is worth.

As noted by colleague @Motta , if there is any null value in the fields, the total result is canceled. If this is the case, you can zero the null values like this:

Instead of using:

SUM( coluna1 + coluna2 ...

Give preference to:

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

The COALESCE cascades values in the parameter list until they are over or a null is found. In our case, since we only use two, the first one is returned, unless it is null, when the 0 is returned instead.