need help with sub 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)

need help with sub totals

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2011-10-07 : 18:08:27

Hi,

i have a report with about 100 lines and i'm trying to calculate sub totals but running into problems:

Select Area, Manufac, Status_Code, count(field) as Count
from table1
group by area, manufac, status_code
having area in('555', '758', '898')

output: I would to sum the Count Field and provide a grand total per Area: For example, i want a row inserted that says for area 555, the sub total should be 249, for Area 758, sub total 555 etc...

curent output
Area: Manufac Status_Code Count
555 F D 200
555 G D 49
758 F D 332
758 G D 223
898 F D 232
898 G D 892

Desired output

Area: Manufac Status_Code Count
555 F D 200
555 G D 49
subtotal
758 F D 332
758 G D 223
subtotal
898 F D 232
898 G D 892
subtotal

joe8079
Posting Yak Master

127 Posts

Posted - 2011-10-07 : 18:09:16

i apologize if the colums got messed up

m_imran18
Starting Member

14 Posts

Posted - 2011-10-07 : 18:46:09

Try this,

Drop table test
Go
Create table test
(
[ID] int identity (1,1),
Area varchar(10),
Manufac varchar(1),
Status_Code varchar(1),
)

Insert into test Values ('555','F','D')
Insert into test Values ('555','F','D')
Insert into test Values ('555','F','D')
Insert into test Values ('555','F','D')
Insert into test Values ('555','F','D')

Insert into test Values ('555','G','D')
Insert into test Values ('555','G','D')
Insert into test Values ('555','G','D')

Insert into test Values ('758','F','D')
Insert into test Values ('758','F','D')
Insert into test Values('758','F','D')
Insert into test Values ('758','F','D')

Insert into test Values ('758','G','D')
Insert into test Values ('758','G','D')
Insert into test Values ('758','G','D')

Insert into test Values ('898','F','D')
Insert into test Values ('898','F','D')
Insert into test Values('898','F','D')

Insert into test Values ('898','G','D')
Insert into test Values ('898','G','D')

Go
Select (Case WHEN (Grouping(Area)=1) THEN 'Area Total' ELSE Area END) AS Area
,(Case WHEN (Grouping(Manufac)=1) THEN 'Manufac Total' ELSE Manufac END) AS Manufac
, Status_Code
, count(*) as Count
,SUM(1) as [SUM]
from test
Where [Area] In ('555', '758', '898')
group by area, manufac, status_code with rollup
Having (Grouping(Manufac)+Grouping(Status_Code))<>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 01:02:26

use with rollup.
see
http://databases.about.com/od/sql/l/aacuberollup.htm

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources