Rollup function - Replace NULL

Question

I seem to be having trouble with the final pieces of my SQL. When implementing the group by with roll up function within my SQL NULL appears. How can one modify NULL so that it is replaced with TOTAL within this aggregated SQL?

Current table returned
Name Activate Deactivate
Max      5       2
TAX     12       5
NULL    17       8

select 
    case 
    when (upper(m.email) like '%max.com') then 'MAX'
    when (upper(m.email) like '%tax.com') then 'TAX'
    else 'OTHER'
end Name, 
SUM(case when substring(convert(varchar(8),n.created_on,112),1,6) = '201209' then 1 else 0         end) 'Activated',
   SUM(case when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209' then 1 else 0              end) 'Deactivated'
           from membership.user_details d
          inner join membership.aspnet_membership m 
on m.userid = d.userid
        inner join membership.user_notes n 
         on n.userid = d.userid
        and n.CREATED_ON = (select min(created_on) 
                    from membership.user_notes 
                    where userid = n.userid
                        and note = 'received.')  
         where approved = 1
         group by case when (upper(m.email) like '%max.com') then 'MAX'
      when (upper(m.email) like '%tax.com') then 'TAX'
      else 'OTHER' end     
   with RollUp 

Answer 1

Use this in place a straight Name reference:

case when grouping(Name) then 'Total' else Name end as Name

If only a single level of grouping is being used, a test for null suffices; hoowever the grouping(...) test allows different descriptions to be generated for each distinct grouping level.

Answer 2

instead of this part

case when (upper(m.email) like '%max.com') then 'MAX' when (upper(m.email) like '%tax.com') then 'TAX' else 'OTHER' end Name

simply use

upper(REPLACE(COALESCE(m.email, 'other'), '.com', '')) as Name

no need to write a sub-query and complicate it further.

Answer 3

You can wrap your case statement with an isnull function:

select 
    isnull
        (
        case 
            when (upper(m.email) like '%max.com') then 'MAX'
            when (upper(m.email) like '%tax.com') then 'TAX'
            else 'OTHER'
        end,
        'Total'
        ) Name, 
    SUM(case
            when substring(convert(varchar(8),n.created_on,112),1,6) = '201209'
            then 1
            else 0
        end) 'Activated',
    SUM(case
            when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209'
            then 1
            else 0
        end) 'Deactivated'
from membership.user_details d
    inner join membership.aspnet_membership m 
        on m.userid = d.userid
    inner join membership.user_notes n 
        on n.userid = d.userid
        and n.CREATED_ON =
            (
            select min(created_on) 
            from membership.user_notes 
            where userid = n.userid
                and note = 'received.'
            )  
where approved = 1
group by
    case
        when (upper(m.email) like '%max.com') then 'MAX'
        when (upper(m.email) like '%tax.com') then 'TAX'
        else 'OTHER'
    end     
with rollup

Answer 4

The ROLLUP places a null in the totals row so if you want to replace that I would suggest taking your existing query and placing it in a subquery and then use a CASE on the NAME to replace the null to Total.

You code will be similar to this:

select 
  case when name is null then 'Total' else name end Name,
  sum(Activated) Activated,
  sum(Deactivated) Deactivated
from
(
  select 
    case 
      when (upper(m.email) like '%max.com') then 'MAX'
      when (upper(m.email) like '%tax.com') then 'TAX'
      else 'OTHER'
    end Name, 
  SUM(case when substring(convert(varchar(8),n.created_on,112),1,6) = '201209' then 1 else 0         end) 'Activated',
  SUM(case when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209' then 1 else 0              end) 'Deactivated'
  from membership.user_details d
  inner join membership.aspnet_membership m 
    on m.userid = d.userid
  inner join membership.user_notes n 
    on n.userid = d.userid
    and n.CREATED_ON = (select min(created_on) 
                        from membership.user_notes 
                        where userid = n.userid
                          and note = 'received.')  
  where approved = 1
  group by case when (upper(m.email) like '%max.com') then 'MAX'
        when (upper(m.email) like '%tax.com') then 'TAX'
        else 'OTHER' end     
) src
group by name with rollup

If you do not wrap your query in a subquery, then you can use something like this:

select
   case when 
     (case 
        when (upper(email) like '%max.com') then 'MAX'
        when (upper(email) like '%tax.com') then 'TAX'
        else 'OTHER'
      end) is null then 'Total'
      else case 
        when (upper(email) like '%max.com') then 'MAX'
        when (upper(email) like '%tax.com') then 'TAX'
        else 'OTHER'
      end end Name,
  SUM(case when substring(convert(varchar(8),n.created_on,112),1,6) = '201209' then 1 else 0 end) 'Activated',
  SUM(case when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209' then 1 else 0 end) 'Deactivated'
from membership.user_details d
inner join membership.aspnet_membership m 
  on m.userid = d.userid
inner join membership.user_notes n 
  on n.userid = d.userid
  and n.CREATED_ON = (select min(created_on) 
                     from membership.user_notes 
                     where userid = n.userid
                       and note = 'received.')  
where approved = 1
group by case when (upper(m.email) like '%max.com') then 'MAX'
        when (upper(m.email) like '%tax.com') then 'TAX'
        else 'OTHER' end with rollup