Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem with "group by"

 
Marilyn de Queiroz
Sheriff
Posts: 9067
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Marilyn de Queiroz
Sheriff
Posts: 9067
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9067
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1821
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9067
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting the fields and printing to a flat file. Now I need to add new fields to the same file.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic