apitech
asked on
Invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Hello:
I am getting the following error, upon running the attached code.
Msg 8120, Level 16, State 1, Line 1
Column 'HATB2.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
My T-SQL troubleshooting skills are not good, so that's why I'm asking.
I agree that this is a long select statement. But, I sort of know what the problem is and I know how to point you to a "summary" of this.
If you do a "find" on the following clauses in the attached select statement, you will see that this long select statement is actually three sets of select statements for three separate databases (TRXDE, TRXIN, and TRXUK):
--TRXIN BEGINS HERE:
--TRXUK BEGINS HERE:
Again, doing a search on the two clauses that I mention above will help you see the "separation" among the three databases.
Anyway, the error is referring to the first line of the long select statement which ultimately refers to the very end of the long select statement.
Somehow, it does not like the reference to HATB2. This reference is in three spots throughout this long select statement. That's probably the issue.
Really, it's got to be just some simple T-SQL syntax change with ORDER BY and GROUP BY. But, I'm not skilled enough to see what the fix is.
If I run the three "sets" of select statements individually, the correct data is returned. It's combining them in this select statement that is generating this error.
Perhaps, I need to add another select clause at the beginning or change HATB2 to HATB3, HATB4, etc.
I hope that makes sense.
Any help is much appreciated!
Apitech
why.txt
I am getting the following error, upon running the attached code.
Msg 8120, Level 16, State 1, Line 1
Column 'HATB2.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
My T-SQL troubleshooting skills are not good, so that's why I'm asking.
I agree that this is a long select statement. But, I sort of know what the problem is and I know how to point you to a "summary" of this.
If you do a "find" on the following clauses in the attached select statement, you will see that this long select statement is actually three sets of select statements for three separate databases (TRXDE, TRXIN, and TRXUK):
--TRXIN BEGINS HERE:
--TRXUK BEGINS HERE:
Again, doing a search on the two clauses that I mention above will help you see the "separation" among the three databases.
Anyway, the error is referring to the first line of the long select statement which ultimately refers to the very end of the long select statement.
Somehow, it does not like the reference to HATB2. This reference is in three spots throughout this long select statement. That's probably the issue.
Really, it's got to be just some simple T-SQL syntax change with ORDER BY and GROUP BY. But, I'm not skilled enough to see what the fix is.
If I run the three "sets" of select statements individually, the correct data is returned. It's combining them in this select statement that is generating this error.
Perhaps, I need to add another select clause at the beginning or change HATB2 to HATB3, HATB4, etc.
I hope that makes sense.
Any help is much appreciated!
Apitech
why.txt
There are two group by statements commented in the attached file. Try uncommenting them one at a time and check if that resolves the issue.
If not check other group by clauses and add CustomerID where missing.
If not check other group by clauses and add CustomerID where missing.
ASKER
viralypatel: That doesn't work. I need those group by statements, and I had already commented out the ones that were originally causing a problem. Anything else?
aarontomosky: Yes, please do let me know ASAP. I'm really running low on time.
All: It's gotta have something to do with parentheses, or needing to add another select clause at the top, or something along those lines. Sigh...I really with my company would let me go to a T-SQL programming class.
aarontomosky: Yes, please do let me know ASAP. I'm really running low on time.
All: It's gotta have something to do with parentheses, or needing to add another select clause at the top, or something along those lines. Sigh...I really with my company would let me go to a T-SQL programming class.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
since you have "sum()" in your select, you must have group by. Like tim said, line 789 needs to be uncommented.
Line 1581 as well.
ASKER
Thank you, tim! That was it!
Apitech
Apitech
When you have a group by you have to have every single field either in the group by or in an aggregate function (max, min, count, sum, etc...)
When you have joins, it's sometimes easier to do the group by first in a subquery and then select from that and join your extra "information" columns.
If you still can't figure this out I'll have a look when I get to a PC.