?
Solved

How to get Grand Total column using WITH ROLLUP clause?

Posted on 2013-06-12
16
Medium Priority
?
1,472 Views
Last Modified: 2013-06-14
I am using SQL Server 2008.

I have a T-SQL code in which I am using WITH ROLLUP clause to get Grand Total as footer row and last column. With the below code I am getting last row as Grand Total but I am not getting last column as Grand Total.

Is it possible to use WITH ROLLUP to get Grand Total column as well?

Below is the extract of my T-SQL code. The second last line is where I am using the WITH ROLLUP clause.
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)

Open in new window

0
Comment
Question by:rpkhare
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39242638
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

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 39242810
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243443
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 39244084
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.
0
 
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 300 total points
ID: 39244166
If I am not mistaken, the change is simple.

Change Line 35:
set @sql3 = 'select Country,' + @SumCols + ' from #TempReport group by Country WITH ROLLUP'

To:
set @sql3 = 'select isnull(Country, ''Grand Total'') as Country,' + @SumCols + ' from #TempReport group by Country WITH ROLLUP'

I hope that helps!

P.S. I read the question as WITH ROLLUP is working as expected, but the term "Grand Total" was not included. The reason is the rollup is for ALL countries, so Country field is NULL on the total row. Therefore, ISNULL() or COALESCE() works to relabel the row as desired.

MSDN: http://msdn.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39245379
@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,'')

Open 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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39245540
@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.
0
 
LVL 49

Expert Comment

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

Open 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 }
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39246619
@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?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39246626
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)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39246640
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

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 39246671
see this working at: http://sqlfiddle.com/#!3/f21e1/1
Here is a working simulation of your overall query that does add the extra column
declare @Gtot as varchar(max)
select @Gtot = ', sum(' 
               + 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,'')
               + ') as GrandTotal'

--select @Gtot

declare @SumCols as varchar(max)
set @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,'')

--select @SumCols

declare @sql3 as varchar(1000)
set @sql3 =  'select isnull(Country,''Grand Total'') as Country, '
            + @SumCols 
            + @Gtot
            + ' from TempReport group by Country WITH ROLLUP'

--select @sql3
exec(@sql3)
;

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 39248454
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.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39248487
@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)

Open in new window


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

Anyway, thanks for your inputs.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39249518
>>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.
Whilst you were unable to get the full concatenation to work - and found a workable alternative - I was intrigued by the B grading and noticed you are quite consistent with using that. Here is an E-E article on this topic: What grade should I award?
0
 
LVL 8

Author Comment

by:rpkhare
ID: 39249532
>> 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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question