Solved

How to get Grand Total column using WITH ROLLUP clause?

Posted on 2013-06-12
16
1,382 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 26

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 48

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
 
LVL 26

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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 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 26

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 48

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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 48

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 48

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 48

Accepted Solution

by:
PortletPaul earned 400 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 59

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 48

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now