forum

Home / DeveloperSection / Forums / SQL Rename NULL

Ask Question

SQL Rename NULL

Anonymous User413203-Apr-2013
Hi All!

I searched and tried alot now, to figure out how to replace the name 'NULL' in a table, when I run this query:

SELECT 
YEAR(orderdate) AS Years,
       (CASE WHEN country = 'US' THEN 'US' ELSE 'WORLD' END) AS region,
   SUM(netamount) AS TotSales
FROM orders o JOIN
     customers c 
     ON o.customerid = c.customerid
GROUP BY  (CASE WHEN country = 'US' THEN 'US' ELSE 'WORLD' END),YEAR(orderdate) WITH ROLLUP;
I get this table: http://imgur.com/pzHa8fK

I want to replace the Nulls to 'SubTotal' respectively 'GrandTotal'. I tried it with:

COALESCE(year(orderdate), 'Subtotal') years,...
and the same with 'IFNULL(...)' but instead of replacing the name, it makes an extra column with years where all the years are listed again and the NULL's 

are still remaining.

Any idea?

Advance Thanks!
mssql servermssql server 
Updated on 03-Apr-2013
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

03-Apr-2013
Hi Tanuj!

Try this way 

One way you can do this is to wrap your query in another select and then replace the null values with a COALESCE or a CASE expression:

select 
    case 
        when Years is null and region is null then 'GrandTotal'
        when Years is null then 'SubTotal' 
        else Years end Years,
    coalesce(region, '') region,
    TotSales
from
(
    SELECT YEAR(orderdate) AS Years,
       (CASE WHEN country = 'US' THEN 'US' ELSE 'WORLD' END) AS region,
       SUM(netamount) AS TotSales
    FROM orders o JOIN
         customers c 
         ON o.customerid = c.customerid
    GROUP BY  (CASE WHEN country = 'US' THEN 'US' ELSE 'WORLD' END),YEAR(orderdate) WITH ROLLUP
) 

I hope it resolve your problem

Liked By