By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,659 Members | 1,750 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,659 IT Pros & Developers. It's quick & easy.

Group by in select problem

P: 167
I am having problems with a query whenever I use 'group by', it tells me that the other columns I have are not part of an aggregate function.
Expand|Select|Wrap|Line Numbers
  1. Select Acctnum, Date, Dept, sum(SPAmt)
  2. From TableA
  3. Group by Dept
The result I am trying to get is a row for each Dept with the total of SPAmt for that Dept. However, when I run the select, it gives an error and says 'Acctnum' is not part of an aggregate function. What am I doing wrong?

May 27 '09 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,712
I'm not sure where this would be confusing so I'll explain as clearly as I can.

GROUP BY, as a clause, ensures that a list of one or more input records are processed to provide a single output record.

Any item within the GROUP BY clause is one that, by definition, must be the same across the group (of input records). Thus SELECTing any such field makes sense without aggregation.

Aggregation is a process that returns a single value from one or more values provided. Examples of aggregate functions include Sum(), First(), Max(), etc.

A GROUP BY query needs either a GROUPed by item or an aggregate item. In your SQL you refer to AcctNum without any aggregation or GROUP BY. There is no way for the SQL engine to interpret or process this. Hence the error message.

Does this make things clearer?
May 27 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.