Mysql display the sum field with null value

Asked

Viewed 2,448 times

2

I’ve researched but I can’t find what I need.

I have a chart where I have a record of debt and credit releases. These values are in the 'value' field. But I want to display this field in two columns, one for debit and one for credit.

I got:

SELECT L.REGISTRO, C.CODCADASTRO, CRE.VALOR AS CREDITO, DEB.VALOR AS DEBITO 
   FROM CADASTRO C 
      JOIN LANCAMENTOS L ON C.CODCADASTRO = L.CODFAVORECIDO       
      LEFT JOIN LANCAMENTOS CRE ON CRE.REGISTRO = L.REGISTRO AND CRE.TIPOLANCAMENTO = 0
      LEFT JOIN LANCAMENTOS DEB ON DEB.REGISTRO = L.REGISTRO AND DEB.TIPOLANCAMENTO = 1

I want to add one more column to add the two together, and that’s where I can’t. The column that has the debit I get NULL in credit and the same happens with the credit column. The sum of the records of these columns is coming NULL.

CRE.VALOR AS CREDITO, DEB.VALOR AS DEBITO, (CRE.VALOR + DEB.VALOR) AS SOMA

So my problem is: How to sum the values of fields per record where a field is NULL?

Att.

mysql select-sql

  • Do you want to add a value with NULL? type 1+null??

  • Yes, this is because a LEFT JOIN generated a NULL value in one of the columns and I was unable to perform the sums.

1 answer

5


The COALESCE That’s what it’s good for:

CRE.VALOR AS CREDITO, DEB.VALOR AS DEBITO,
  ( COALESCE( CRE.VALOR, 0 ) + COALESCE( DEB.VALOR, 0 ) ) AS SOMA

The syntax is:

COALESCE( v1, v2, v3, ... )

Basically this function causes the first value of the list that is not null to be used.

Examples:

SELECT COALESCE( null, 2, null, 4 )             => 2
SELECT COALESCE( null, null, null, 'a' )        => 'a'
SELECT COALESCE( 1, 2, 3, 4 )                   => 1

SELECT COALESCE( 1, 2 ) + COALESCE( 4, 8 )      => 5
SELECT COALESCE( null, 2 ) + COALESCE( 4, 8 )   => 6
SELECT COALESCE( 1, 2 ) + COALESCE( null, 8 )   => 9

See working on SQL Fiddle.

  • Thank you very much, that’s just what I needed.

  • If you need it, you can use it for aggregation as well: SELECT SUM( COALESCE( DEB.VALOR, 0 ) ) AS TOTALGERAL

  • This table is a bank statement. My next step is to update the balance, for this, in this sum column, I have to take the value of the previous row and add with the current row and display the sum in the BALANCE column. But I must do within the program, because the zero record is the initial balance of the statement.

  • You can do with darlings also, adding the previous value as a constant. Anyway, there are cases where it is better to treat in the same application.

Browser other questions tagged mysql select-sql

You are not signed in. Login or sign up in order to post.