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
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
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.
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
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.
