Sunday, February 12, 2012

change null row of roll up query

hi all

i m using rollup statement in my query. it gives me total field with null caption.

is it possible that i can change this null field with caption "total"

thanks & regards

ganesh

If I understand you question you could use following statement:

<your total field expression> as "Total"

Please, show your query

|||

Yes you can.. use the reference as the bellow query

Code Snippet

Declare @.data Table
(
Region Varchar(100),
Country Varchar(100),
Sales float
);

Insert Into @.data Values ('EMEA', 'France', 100);
Insert Into @.data Values ('EMEA', 'France', 200);
Insert Into @.data Values ('EMEA', 'Germany', 56);
Insert Into @.data Values ('EMEA', 'Germany', 12);
Insert Into @.data Values ('EMEA', 'UK', 1256);
Insert Into @.data Values ('EMEA', 'UK', 1212);
Insert Into @.data Values ('APJ', 'Japan', 130);
Insert Into @.data Values ('APJ', 'Japan', 32);
Insert Into @.data Values ('APJ', 'China', 256);
Insert Into @.data Values ('APJ', 'China', 212);
Insert Into @.data Values ('APJ', 'India', 23);
Insert Into @.data Values ('APJ', 'India', 232);
Insert Into @.data Values ('America', 'US', 23);
Insert Into @.data Values ('America', 'US', 23432);
Insert Into @.data Values ('America', 'Canada', 223256);
Insert Into @.data Values ('America', 'Canada', 2122);
Insert Into @.data Values ('America', 'Brazil', 23232);
Insert Into @.data Values ('America', 'Brazil', 223232);

Select
isnull(Case When Country is NULL Then Region + ' Total' Else Region End,'Grand Total') Region,
isnull(Country,'') Country,
Sum(Sales)
From
@.Data
Group By
Region,Country With Rollup

No comments:

Post a Comment