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

change null field of rollup summary

hi all

i m using sql server 2005. i m using rollup query to get total sum and sum with group within single query. it shows me total amount but with null field . i want to change this null field with "total " caption.

pls suggest how this thing is possible.

thanks and regards

Ganesh

Moved to t-sql forum...|||

That is the way a Rollup query works, and, unfortunately, as far as I know, there is no way to get around it.

Of course, your client side application could easily do that. (After all, presentation is the point of client applications, data storage and retrieval is the point of SQL Server.)

|||

Use the GROUPING function in the SELECT list like:

CASE GROUPING(col1) WHEN 1 THEN 'Total' ELSE Col1 END

See Books Online for more details. Or post your sample query here to get the corrected one.

|||Good Idea Umachardar, I consider it, but discarded the idea since it didn't quite seem to fit the OP's request. But perhaps it will.