Home > DeveloperSection > Forums > SQL Rename NULL
Tanuj Kumar

Total Post:134

Points:940
Posted on    April-03-2013 2:41 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 1998  View(s)
Rate this:
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!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    April-03-2013 8:14 AM

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


Don't want to miss updates? Please click the below button!

Follow MindStick