group by - 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 2005 Forums

Transact-SQL (2005)

group by

Author

Topic

nitinnjp
Starting Member

6 Posts

Posted - 2010-07-29 : 07:17:27

i have query of regarding group by,

Following is my Query

select TerritoryName,ClassGrade,IsLoyal,count(IsLoyal) PartyCount,sum(Convert(int,AveragePwgConsumption)) SumavgCons
sum
from VerticalPartyMaster vpm
inner join TerritoryMaster tm
on vpm.TerritoryID=tm.TerritoryID
inner join VerticalDetails vd
on vpm.vid=vd.vid
inner join ClassMaster cm
on cm.ClassID=vd.ClassID
where
vpm.PartyTypeID=1
or
vpm.PartyTypeID=5
group by TerritoryName,ClassGrade,IsLoyal
,AveragePwgConsumption

it is giving output like this

TerritoyName Classgrade isLoyal parycount SumavgCount
north A 1 5 200
north A 1 2 200
East A 1 5 50
East A 1 5 200

Expected output is like

TerritoyName Classgrade isLoyal parycount SumavgCount
north A 1 5 200
north A 1 2 200
north total 400
East A 1 5 50
East A 1 5 200
East total 250

(In above TerritoyName,Classgrade,isLoyal,parycount, SumavgCount these are 5 columns.i want sum territoy wise)

how can i achieve this please tell me..Thanks in advance

nitin patil

Llycas
Starting Member

4 Posts

Posted - 2010-07-29 : 11:30:18

Just had a quick look - kinda busy at the mo.

But it appears your problem can be solved by an aggregate function on your Parycount field.

Judging by your WHERE clause, a simple MAX(parycount) in your SELECT could solve your problems.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-29 : 14:49:58

i don't know your database schema or i don't have a sample data, but looking at your query you are grouping columns that are summoned or counted in select list. try this:


select 
TerritoryName
,ClassGrade
,IsLoyal
,count(IsLoyal) PartyCount
,sum(Convert(int,AveragePwgConsumption)) SumavgCons
sum
from VerticalPartyMaster vpm
inner join TerritoryMaster tm
on vpm.TerritoryID=tm.TerritoryID
inner join VerticalDetails vd
on vpm.vid=vd.vid
inner join ClassMaster cm
on cm.ClassID=vd.ClassID
where
vpm.PartyTypeID=1 or vpm.PartyTypeID=5
group by 
TerritoryName
,ClassGrade
,IsLoyal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources