Simple grouping question - 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)

Simple grouping question

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2008-06-05 : 10:44:27

I'm sure this is very basic, I'll try not to be too embarrassed asking it.

Given a table called MainTable, and a second table called Details, with a one-to-many relationship between them, which is the better practice in regards to getting a number of values from each MainTable record plus an aggregate of a value from the related Details records...?


--=====================================

--Method 1
--Group by every selected field in MainTable
SELECT MainTable.Rec_Num,
       MainTable.Name,
       MainTable.Description,
       SUM(Details.Amount) AS Amount
FROM MainTable
INNER JOIN Details
ON MainTable.Rec_Num = Details.MainTable_Rec_Num
GROUP BY MainTable.Rec_Num, MainTable.Name, MainTable.Description

--Method 2
--Group by PK record in MainTable and aggregate the others
SELECT MainTable.Rec_Num,
       MAX(MainTable.Name) AS [Name],
       MAX(MainTable.Description) AS [Description],
       SUM(Details.Amount) AS [Amount]
FROM MainTable
INNER JOIN Details
ON MainTable.Rec_Num = Details.MainTable_Rec_Num
GROUP BY MainTable.Rec_Num

--=====================================

Or perhaps there's another option I'm not aware of?

Thanks!
Ron Moses
ConEst Software Systems

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-05 : 11:59:39

my instinct is the second may go a little faster as it's only sorting by the PK instead of PK, Name, Description.

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 12:37:01

Method two may not return all column value form same record...

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-05 : 13:23:39

if Rec_Num is a PK, how could GROUP BY MainTable.Rec_Num be any different from GROUP BY MainTable.Rec_Num, MainTable.Name, MainTable.Description?

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 14:22:56

I think both methods would work fine as Rec_Num is PK you will have only unique records per Rec_num. So grouping by Rec_Num and applying aggregation for others will yield the same result as grouping on all fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources