Group by Query Problem - 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 Query Problem

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2010-04-11 : 17:58:42

Here is my query
Actually there are multiple rows with PartCode But I've to Group it by MonComm.PartCode. It should then show the sum of all fields.

Select MonComm.PartCode,MonComm.BrokRage,MonComm.CompShare,MonComm.LessTax,MonComm.Compbal,MonComm.LessExpense,MonComm.cBal,MonComm.Comm,MonComm.LessTDs,MonComm.NetComm,ePartners.PartName, ePartners.BankName, ePartners.BankAc, ePartners.BankBranch, ePartners.IFSCCode, ClientDets.ClientName as [Client] from MonComm Inner Join ePartners on MonComm.PartCode = ePartners.PartCode Inner Join ClientDets on MonComm.KarvyCode=ClientDets.KarvyCode Where MonComm.Date1 >= @FDate_1 AND MonComm.Date2 <= @TDate_2

How could I?

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 03:13:09

Start with

select MonComm.PartCode,sum(col2),sum(col3),... from your_table
where....
group by MonComm.PartCode

Madhivanan

Failing to plan is Planning to fail

ravininave
Posting Yak Master

111 Posts

Posted - 2010-04-12 : 12:22:10

quote:
Originally posted by madhivanan

Start with

select MonComm.PartCode,sum(col2),sum(col3),... from your_table
where....
group by MonComm.PartCode

Madhivanan

Failing to plan is Planning to fail

I've tried this but it's not working.

Select MonComm.PartCode,sum(MonComm.BrokRage),sum(MonComm.CompShare),sum(MonComm.LessTax),sum(MonComm.Compbal),sum(MonComm.LessExpense),sum(MonComm.cBal),sum(MonComm.Comm),sum(MonComm.LessTDs),sum(MonComm.NetComm),ePartners.PartName, ePartners.BankName, ePartners.BankAc, ePartners.BankBranch, ePartners.IFSCCode, ClientDets.ClientName as [Client] from MonComm
Inner Join ePartners on MonComm.PartCode = ePartners.PartCode
Inner Join ClientDets on MonComm.KarvyCode=ClientDets.KarvyCode
Where
MonComm.Date1 >= @FDate_1 AND MonComm.Date2 <= @TDate_2
Group By
MonComm.PartCode

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:32:29

wont work unless you apply some aggregation over ePartners field.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:34:48

alternatively you could do


SELECT m.*
,ePartners.PartName, ePartners.BankName, ePartners.BankAc, ePartners.BankBranch, ePartners.IFSCCode, ClientDets.ClientName as [Client] 
FROM
(Select MonComm.PartCode,MonComm.KarvyCode,sum(MonComm.BrokRage),sum(MonComm.CompShare),sum(MonComm.LessTax),sum(MonComm.Compbal),sum(MonComm.LessExpense),sum(MonComm.cBal),sum(MonComm.Comm),sum(MonComm.LessTDs),sum(MonComm.NetComm)
from MonComm 
Where MonComm.Date1 >= @FDate_1 AND MonComm.Date2 <= @TDate_2 
Group By MonComm.PartCode,MonComm.KarvyCode)m
Inner Join ePartners on m.PartCode = ePartners.PartCode 
Inner Join ClientDets on m.KarvyCode=ClientDets.KarvyCode

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources