How can I replace the NULL with a text string in a query in sql?

Question

How can I replace the NULL with a text string in a query in sql?

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

SELECT MONTH(pag_fecha) AS Mes, SUM(pag_importe) AS Ingresos
FROM PAGO
GROUP BY MONTH(pag_fecha) WITH ROLLUP

I would like instead of NULL to get me "Total income"

I already tried it with a CASE but I want to know if there was a simpler way to do it since in the CASE I have to put all the months so that it shows me the result I want

SELECT CASE MONTH(pag_fecha)
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
WHEN 8 THEN '8'
WHEN 9 THEN '9'
WHEN 10 THEN '10'
WHEN 11 THEN '11'
WHEN 12 THEN '12'
ELSE 'Total General'
END AS Mes,
SUM(pag_importe) AS Ingresos
FROM PAGO
GROUP BY MONTH(pag_fecha) WITH ROLLUP

sql-server

asked by Franco Zaconetta Gosicha 30.04.2018 в 04:51

source

2 answers

1

This could be achieved with a% simple% co_, we would validate when (when) the field case is pag_fecha , then (then) we show the text "Total income" , otherwise (else) we simply show the month of the date with null

Ejm

SELECT CASE WHEN pag_fecha IS NULL 
        THEN "Ingresos totales" 
        ELSE MONTH(pag_fecha)
        END AS Mes
from PAGO

DEMO

answered by 30.04.2018 в 05:25

It does not compare well the string (I think). Java CompareTo () How do I install angle 4?

score 0 · Accepted Answer

A more compact form is the following:

SELECT ISNULL(CONVERT(VARCHAR,MONTH(pag_fecha)), 'Total General') AS Mes,
       SUM(pag_importe) AS Ingresos
       FROM PAGO
       GROUP BY MONTH(pag_fecha) WITH ROLLUP

It is necessary to convert the month into varchar ( CONVERT(VARCHAR,MONTH(pag_fecha)) ) because in the same column we will have a text for the general total and finally, with ISNULL(<valor columna>, <valor si es null>) we add the text Total General just for this row