How to get Grand Total column using WITH ROLLUP clause?

Run the bellow script to actually print the query stored in the @sql3 variable and post it here:

declare @sql as varchar(max)
set @sql = 'insert into #TempReport select [Country],' + @cols + '
	from
	(
		select 
			C.Country_Name AS [Country],
			D.GSL_Name AS [GSL],
			SUM(A.Allocation) AS Total
			from Work_Information A
			inner join Sales B
			on A.Sales_ID = B.Sales_Id
			inner join Countries C
			on A.Country_ID = C.Country_ID
			inner join tbl_GSL D
			on A.GSL_ID = D.GSL_ID AND A.Sales_ID = D.Sales_Id
			where A.Sales_Id = 1
			group by A.Country_ID,C.Country_Name, A.Sales_ID, A.GSL_ID,D.GSL_Name
	) P Pivot (sum(Total) for [GSL] in (' + @Cols + ')) as Pvt'

exec(@sql)

declare @SumCols as varchar(max)
select @SumCols = stuff(
	(select ', Sum(' + quotename(GSLName) + ')' + REPLACE(GSLName,' ','')  from 
		(
			select TOP 200 GSL_Name as GSLName 
			from tbl_GSL GSL
			where Sales_Id = 1
			ORDER BY GSL_Name
		) GlobalSales 
		ORDER BY GSLName FOR XML PATH('')
	),1,2,'')

declare @sql3 as varchar(1000)
set @sql3 = 'select Country,' + @SumCols + ' from #TempReport group by Country WITH ROLLUP'
-- exec(@sql3)
print @sql3

Select allOpen in new window

Hi,

I've found that this properly belongs in the presentation layer, and is way easier, more control with things like Crystal Reports et al.

HTH
  David

is it me? I don't see where is @cols determined in the posted code. In addition to seeing the final dynamic sql - I'd like to see how @cols is generated please.

and I cannot disagree with dtodd above - all too often facilities in the presentation layer get overlooked - so, what is the purpose of the overall query? is it a report? (and if a report what is being used?)

summary:
include the code that generates @cols
post the actual generated code @sql3
what is the queries' purpose?
if a report - what is used as the reporting tool?

The reason for dynamic construct is obliviously the PIVOT part. As for presentation layer, maybe the asker has no intend to use any,  maybe he just wants a csv file. Anyway that is irrelevant to the question.

@PortletPaul:

@Cols code is below:

declare @Cols as varchar(max)
select @Cols = stuff(
	(select ',  ' + quotename(GSLName)   from 
		(
			select TOP 200 GSL_Name as GSLName 
			from tbl_GSL GSL
			where Sales_Id = 1
			ORDER BY GSL_Name
		) GlobalSales 
		ORDER BY GSLName FOR XML PATH('')
	),1,2,'')

Select allOpen in new window

The purpose of the query is to generate final report with result-set including Grand Total. I don't want to do this from front-end and I am not using any reporting tool. I just want to display in GridView.

I don't have the actual data in hand to show @sql3, but it looks like this:

select Country, Learning, Pharma, Beverages from #TempReport group by Country WITH ROLLUP

Select allOpen in new window

@rpkhare

If you run the code I posted above will actually print the query in @sql3 and this way you we can see exactly what the problem is.

                                             ?? Q1 ??
Country     GSLName1 GSLName2 ... GSLName(n) GrandTotal
Australia   100      100          100        300
USA         100      100          100        300
Grand Total 200      200          200        600
?? Q2 ??

Select allOpen in new window

taking the clue from mwvisa1, let's just clarify what the question wording means perhaps.

Q1: are you seeking an additional column (as shown to the right above)?
Q2: are you seeking the words "Grand Total" in the last row?
Q3: both the above

{ nb: mwvisa1 has provided a solution for Q2 }

@mwvisa1:

I checked that link where it shows how to deal with nulls in rows.

But how to add Grand Total additional column? Is it possible to add it using WITH ROLLUP?

Not without more effort - it is not an automatic feature of WITH ROLLUP (e.g. how would it know what columns? perhaps some are added and others deducted). WITH ROLLUP adds rows, not columns.

So you do want Q3 (both Q1 and Q2).
I would still like to see the actual output from @sql3, just run the code as proposed so that instead of it being executed it is displayed. copy/paste here.

one other thing: is this for sql server 2008? (you have 2005 listed as well)

please see: http://sqlfiddle.com/#!3/5190d/2

select
  isnull(Country, 'Grand Total') as Country
, sum(GSLName1)  GSLName1
, sum(GSLName2)  GSLName2
, sum(GSLName3)  GSLName3
, sum(GSLName4)  GSLName4
, sum(GSLName5)  GSLName5
, sum(GSLName6)  GSLName6
, sum(GSLName7)  GSLName7
, sum(GSLName8)  GSLName8
, sum(GSLName9)  GSLName9
, sum(GSLName10) GSLName10

/* this "style" of syntax needs to be added for the extra column */
, sum(
      gslname1 
    + gslname2 
    + gslname3 
    + gslname4 
    + gslname5 
    + gslname6 
    + gslname7 
    + gslname8 
    + gslname9 
    + gslname10
     ) as GrandTotal

from TempReport
group by
 country
 with rollup

Select allOpen in new window

Re: But how to add Grand Total additional column? Is it possible to add it using WITH ROLLUP?

As PortletPaul said, WITH ROLLUP will add a row, or a set of rows depending on groupings. If you want totals by row, you will have SUM() the columns as one calculation as shown above. PortletPaul seems to have it covered, but I just wanted to respond to your post with my name in it. Therefore, I may just monitor without saying much after this.

@PortletPaul,

Actually I already tried something like GTot as you suggested. But it was not working when concatenated in @sql3.

So I tried another way which worked.

declare @HTotal as varchar(1000)
set @HTotal = 'ALTER TABLE #TempReport ADD Grand_Total AS ' + @GTot
EXEC(@HTotal)

Select allOpen in new window

Actually I wanted to reduce the code by not using GTot code.

Anyway, thanks for your inputs.

>>But it was not working when concatenated in @sql3.
this is why I was after a visual inspection of the contents of @sql3
the additional concatenation would work provided syntax is correct, I had to tweak your code a little (see line 19 in accepted answer, there's and extra space after a bracket) and the new @Got had to commence with a comma (line 2) - i.e. without "seeing" the generated code it's awkward to get it right in my experience. You'll also notice I left in the various selects I had used to inspect as I proceeded.

Just one other point, there are now 3 places where you are running that 'top 200' subquery you might gain a little by placing it into a cte and include this there:
REPLACE(GSLName,' ','')
this way @Cols, @Gtot and @SumCols are all based on the same subquery which is run just once.

>> I was intrigued by the B grading and noticed you are quite consistent with using that.

Actually B should not be Good in my opinion. For me A+ is Excellent, A is Good and B is a working solution but not too good. Therefore, while giving grades, I look into Excellent, Good and Average and not A,B and C.

Will take it into consideration.