How do I know exactly which rows will need to go into the GROUP BY clause

Question

How do I know exactly which rows will need to go into the GROUP BY clause

adam spencer

Hall of Fame

Points: 3926
More actions
January 9, 2011 at 10:33 am

#232996

Hi,
I meant which Columns not rows. How do I know exactly which columns will need to go into the GROUP BY clause?
Now I am shooting in the dark just putting whatever columns it errors on in GROUP BY till stops complaining.
I want to know ahead of time by looking at my query which columns if any will need to be in
a group by before I get the great error message we have all seen too many times...
Column 'dbo.tblMemberMonthsEligibilityCapitated.Phy' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SO here is my query I commented the last column in the GROUP BY clause to bring up this message, I knowthe PHY column needs to be there.
so here are the questions I would GREATLY appreciate your help with:
a. how can I know ahead of time which columns will need to go in?
b. If I am just putting every columns the error tells me needs to go in, is this a bad practice?
c. If you look at my select , why did it not complain that I am missing the column OKtoSubmit or DOS?
SELECT a.GMPI,b.ID_OTHER_NoDash, b.FirstName, b.LastName, a.Memb, a.ICD9,a.HCC, b.Phy ,OKtoSubmit = MAX(a.OKtoSubmit), DOS = CONVERT(char(6),a.DateOfService,112)
INTO #PAL
FROM dbo.ztblAggICD9 AS a INNER JOIN dbo.tblMemberMonthsEligibilityCapitated AS b
ON a.GMPI = b.GMPI AND CONVERT(char(6),a.DateOfService,112) = b.YearMonth
WHERE (b.Center= 'PY') AND (a.HCC IS NOT NULL)
GROUB BY a.GMPI, b.ID_OTHER_NoDash, b.FirstName, b.LastName, a.Memb, a.DateOfService, a.ICD9, a.HCC--, b.phy
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

GROUP BY is used to perform aggregation.

So you'd need to add every column of your SELECT statement where you don't perform any sort of aggregation (min, max, avg, sum, count).

That basically also explains why OKtoSubmit does not belong to the GROUP BY statement.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

GROUP BY - http://msdn.microsoft.com/en-US/library/ms177673%28v=SQL.90%29.aspx

Edit: wrong copy/paste

Sichy SSC Journeyman Points: 78 More actions · Answer 3

Also any hardcoded values can be excluded from the group by such as col1 below.

SELECT 'value' AS col1,

MAX(col2),

col3

FROM table1

GROUP BY col3

adam spencer Hall of Fame Points: 3926 More actions · Answer 4

Thanks all.

I understand now to the point where I know what needs to go down to the GROUP BY without guessing. Thanks to you all's help!