Re: Another SQL Question

At 9:29 AM -0700 8/13/01, Barry Prentiss wrote:

Thx Alnisa, I had tried your suggested approach earlier. I just tried it again I got the following from SQL*Plus:

Wow, Oracle, what a pain. (Actually, it's probably pure laziness that MySQL doesn't check for this, but anyway). Anyway, I looked up the Oracle errors ORA-00937, and ORA-00979, and they basically both indicate that you can't return a column that isn't included in the Group BY expression. You can't just remove the GROUP BY, because the COUNT requires a GROUP statement. Error 979 basically says line 1, includes a non-grouped expression. Error 937 is the not-a single-group group error, or says basically the same thing. You can look up Oracle errors at: http://otn.oracle.co.kr/docs/oracle78/server.804/a58312/newch220.htm

SQL> select cat.id, cat.name, count(faq_cat.cat_id) as num_id 2 from mdfaq_faq_cat faq_cat, mdfaq_category cat 3 where faq_cat.cat_id = cat.id group by cat.name; select cat.id, cat.name, count(faq_cat.cat_id) as num_id

       *

ERROR at line 1: ORA-00979: not a GROUP BY expression

Applying the above error codes, which indicate that any column returned, must be part of the GROUP function, then the code should look something like: SELECT cat.id, cat.name, COUNT(faq_cat.cat_id) AS num_id FROM faq_cat, cat WHERE faq_cat.id = cat.id GROUP BY cat.name, cat.id or in Oracle speak (as close as I can gather anyway) SQL> SELECT cat.id, cat.name, COUNT(faq_cat.cat_id) AS num_id 2 FROM mdfaq_faq_cat faq_cat, mdfaq_category cat 3 WHERE faq_cat.id = cat.id GROUP BY cat.name, cat.id This just adds the cat.id to the GROUP BY statement. I'm not even certain if that's allowable. But if not remove the cat.id and see if it works, or what error is returned. All these different implementations of SQL occasional kill me. Alnisa -- ......................................... Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org ......................................... Nonprofit Tech E-Update mailto:nonprofit-tech-subscribe@egroups.com ......................................... applying technology to transform .........................................