This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes problem with Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem with "group by"" Watch "problem with "group by"" New topic
Author

problem with "group by"

Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
I'm hoping that someone here can explain to me why this query results in an error:
[Microsoft][SQLServer 2000 Driver for JDBC][SQL Server]Column 'psm1.number' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


I thought at first maybe "number" was a reserved word, but a similar select statement works without any problem.


I note that the first line of each of these is almost identical (except for the aliasing).

Does someone have some insight into this? Why does the first query require a group by and the second one doesn't?


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I think is because you have the aggregate funtion count() in your select clause (though there is a lot going on in there, so I'm not 100% sure!)

Try removing the field:

from your select statement and see if it runs.
[ September 14, 2006: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
Thanks, Paul. That did the trick. Now all I have to do is figure out how to get that field without putting it in the select ...
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You could just leave it in the select and add your other fields to a group by statement. That would work too.
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
The problem is that when I combine the above two statements, I have to also group either assntCon.wlp_company or assneeCon.wlp_company depending on which branch the case follows.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Like Paul's saying, it's the aggregating function count(*).

The presence or absence of an aggregating function in an SQL select statement really dictates how the whole thing works. Anyone writing an SQL statement ever should ask themself at the onset: am I aggregating or not. (<-- Just me being a fanatic here.)

If you have an aggregating function in your select list, you must GROUP BY every other item in the select list. Seriously. (OK, some flavors of SQL will let this slide (the SQL engine in MS Access, if I recall) or let you simplify some expressions or use aliases.) (You can GROUP BY even more things than you have in your select list, too, but that's not important here.)

Part of the reason for this is that how exacly the aggregating function aggregates *depends* on what else is in the select list/group by clause.

So add a group by clause and group by everything in your select list that is not an aggregating function.

MdQ: Column 'psm1.number' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Yep, the SQL engine sees the count(<whatever>) in your select and determines that you're aggregating. It goes to the first item psm1.number, sees that it isn't also being grouped by, and screams at you. (For grins, add "GROUP BY psm1.number" to your query and watch it scream at psm1.severity_code, which is the next item in the list.)

MdQ: The problem is that when I combine the above two statements, I have to also group either assntCon.wlp_company or assneeCon.wlp_company depending on which branch the case follows.

Nope. Group by the whole case statement.

(Why are you combining the two statements? You lost me a little with that.)
[ September 14, 2006: Message edited by: Michael Matola ]
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
I am getting the fields and printing to a flat file. Now I need to add new fields to the same file.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: problem with "group by"
 
Similar Threads
sql server to oracle(sql plus)
retrieve data from huge result set
Old style SQL vs new style SQL
Simplifying a right outer join
counting rows after joins varies