SQL RENAME NULL

Tanuj Kumar

Total Post:134

Points:940
Posted by  Tanuj Kumar
 2567  View(s)
Ratings:
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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL Rename NULL

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!