MySql display the sum field with value null

Navigation
2

I've researched but not found anything I need.

I have a table where I have the record of debit and credit postings. 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 was able to: 

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 and that's where I can not. The column that has the debit is NULL in the credit and so is 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 field values by register where a field is NULL?

Att.

    
asked by anonymous 15.04.2016 / 17:04

1 answer

5

COALESCE is good for this: 

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, ... )

This function basically causes the first non-null list value 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 in SQL Fiddle .

    
15.04.2016 / 17:10
What is a hypothetical programming language? Show / Hide div by changing the icon