This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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
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
Joined: Jul 22, 2000
I am getting the fields and printing to a flat file. Now I need to add new fields to the same file.