[GENERAL] sql query bug???
Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work? The query: select t0.ICD9, t0.Description, count(*) from (select distinct Person_Id, ICD9, Description from PatientDiagnoses) as t0 group by (t0.ICD9, t0.Description) order by count(*) desc limit 10; The error: column t0.ICD9 must appear in the GROUP BY clause or be used in an aggregate function Huh? FWIW, I'm not providing the PatientDiagnoses def because it's a complex query involving 3-way union of 5-way joins--intended for end-user querying. Of note, this query works (and performance is good enough as well): select ICD9, count(*) from (select distinct Person_Id, ICD9 from PatientDiagnoses) as t0 group by ICD9 order by count(*) desc limit 10; -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sql query bug???
Scott Ribe scott_r...@elevated-dev.com writes: Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work? The query: select t0.ICD9, t0.Description, count(*) from (select distinct Person_Id, ICD9, Description from PatientDiagnoses) as t0 group by (t0.ICD9, t0.Description) order by count(*) desc limit 10; The error: column t0.ICD9 must appear in the GROUP BY clause or be used in an aggregate function Drop the parentheses in the GROUP BY. As is, this is requesting to group by the composite value ROW(t0.ICD9, t0.Description). You could argue that that's sufficient to determine both of those column values, but PG doesn't make that deduction (and, I think, is not required to by spec). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sql query bug???
On Feb 5, 2012, at 10:46 PM, Tom Lane wrote: Drop the parentheses in the GROUP BY. I had the suspicion that it was some kind of a late-night brain fart ;-) I don't know where the hell the parens came from, since I've *NEVER* put spurious parens in a group by clause before. But it took someone pointing it out to me to get me to notice that irregularity. Fatigue... One more day of super-crunch and then I get to take a break... Thanks. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
