GROUP BY Query Error - 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 2000 Forums

SQL Server Development (2000)

GROUP BY Query Error

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-05-20 : 09:49:51

I'm getting the following error on this query in SQL Query Analyzer, where Lname is in tblPeople and Balance is from tblAccounting. What am I doing wrong? Thanks!

SELECT (Sum(DebitAmount) - Sum(CreditAmount)) AS Balance, Lname FROM tblAccounting a INNER JOIN tblPeople p ON PeopleID = ID WHERE PeopleID = 23

ERROR: "Column 'p.Lname' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 09:57:12

Why are you using the SUM() function? Are you trying to summarize mutliple rows in your data, or are you just trying to perform some math on some columns in each row?

- Jeff

mtomeo
Starting Member

30 Posts

Posted - 2003-05-20 : 10:02:43

You cannot select a column and use an aggregate function (SUM, COUNT, etc.) on another column in the same Select w/out a Group BY statement. Add "Group By Lname" to the end and you should be golden.

mtomeo
Starting Member

30 Posts

Posted - 2003-05-20 : 10:11:14

and I would recommend using SUM(DebitAmount - CreditAmount) AS Balance. I may be mistaken, but it should run slightly faster w/only one SUM, and they return the same amount.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 12:10:30

SELECT Sum(DebitAmount - CreditAmount) as Balance, Lname

FROM tblAccounting A

INNER JOIN tblPeople P ON PeopleID = ID

WHERE PeopleID = 23

GROUP BY Lname

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-20 : 13:30:28

Something doesn't seem right

quote:

SELECT (Sum(DebitAmount) - Sum(CreditAmount)) AS Balance, Lname FROM tblAccounting a INNER JOIN tblPeople p ON PeopleID = ID WHERE PeopleID = 23

ERROR: "Column 'p.Lname' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

p.Lname is not a reference in the SQL Statement (i.e. there is no alias p associated with that column anywhere)

Or am I losing it?

The SQL is definetly wrong (and SQL team to the rescue with the correct answer), but still...

Brett

8-)

mtomeo
Starting Member

30 Posts

Posted - 2003-05-20 : 14:16:39

sorry Brett...

...look again...it's all there. Apparently Lname is a column on tblPeople...why you would alias the tables when your not going to use it anywhere is beyond me...

 
SELECT (Sum(DebitAmount) - Sum(CreditAmount)) AS Balance, Lname 
FROM tblAccounting a INNER JOIN tblPeople p ON PeopleID = ID 
WHERE PeopleID = 23

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-20 : 15:21:13

Well, I guess it helped out with the error message in that it identified the table the column belong to.

I didn't think it worked that way, but it does. That's a pretty nice feature.


USE NorthWind
GO
CREATE TABLE myTable1(col1 int IDENTITY(1,1), col2 datetime, col3 char(1))
GO
CREATE TABLE myTable2(col4 int IDENTITY(1,1), col5 datetime, col6 char(1))
GO

INSERT INTO myTable1(col3) SELECT 'X'
INSERT INTO myTable1(col3) SELECT 'X'
GO
INSERT INTO myTable2(col6) SELECT 'X'
INSERT INTO myTable2(col6) SELECT 'X'
GO
SELECT * FROM myTable1
GO
SELECT * FROM myTable2
GO

SELECT SUM(col1), col3 FROM myTable1 l INNER JOIN myTable2 r ON col3 = col6
GO 

DROP TABLE myTable1
GO
DROP TABLE myTable2
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources