Subtotals and Grand Totals.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subtotals and Grand Totals..

Author

Topic

gmcnitt
Starting Member

3 Posts

Posted - 2012-03-29 : 16:21:55

Hello all, I have spent the last few hours trying multiple things...finally decided to ask input.....

Sample data:

create table #gmreport (
pin varchar(25),
cdate char(8),
btn varchar(25),
descr varchar(25),
callcount int,
callduration int,
charge money
)
insert #gmreport values
('000000002','20110608','5098544444','Debit','1','25','1.28'),
('000000002','20110621','2063865555','Debit','1','129','1.51'),
('001839930','20110602','2067303333','Debit','2','1080','4.54'),
('001839930','20110603','2067303333','Debit','1','26','1.28'),
('001839930','20110605','2067303333','Debit','1','892','2.91'),
('001839930','20110605','2532247777','Debit','1','811','2.80'),
('001839930','20110608','2532247777','Debit','3','2135','7.68'),
('001839930','20110609','2532247777','Debit','1','867','2.91'),
('001839930','20110610','2532247777','Debit','1','897','2.91'),
('001839930','20110611','2532247777','Debit','2','1799','5.82'),
('001839930','20110614','2532247777','Debit','1','900','2.91'),
('001839930','20110620','2067303333','Debit','1','376','1.98'),
('001839930','20110624','2532247777','Debit','1','387','1.98')

I want to create a report in tsql that will group by pin with subtotals and grand totals at the end, looks like this (with headers of course, just couldnt figure out how to line up here):

000000002 20110608 5098544444 Debit 1 25 $1.28
000000002 20110621 2063865555 Debit 1 129 $1.51
Subtotal 2 154 $2.79

001839930 20110602 2067303333 Debit 2 1,080 $4.54
001839930 20110603 2067303333 Debit 1 26 $1.28
001839930 20110605 2067303333 Debit 1 892 $2.91
001839930 20110605 2532247777 Debit 1 811 $2.80
001839930 20110608 2532247777 Debit 3 2,135 $7.68
001839930 20110609 2532247777 Debit 1 867 $2.91
001839930 20110610 2532247777 Debit 1 897 $2.91
001839930 20110611 2532247777 Debit 2 1,799 $5.82
001839930 20110614 2532247777 Debit 1 900 $2.91
001839930 20110620 2067303333 Debit 1 376 $1.98
001839930 20110624 2532247777 Debit 1 387 $1.98
Subtotal 15 10,170 $37.72

Grand Total 17 10,324 $40.51

I think rollup is required, just can't seem to get it right. thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 16:26:28

why should you do this in t-sql? this can very well be derived using any of reporting tools.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gmcnitt
Starting Member

3 Posts

Posted - 2012-03-29 : 16:30:55

I require this from sql, then attached to a job that will email it out. No reporting tools in the middle.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 16:48:42

you need to use union all then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gmcnitt
Starting Member

3 Posts

Posted - 2012-03-29 : 16:52:10

Could you provide an example.....I have tried ussing rollup, case, cube, just cannot get it right...??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources