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:[EMAIL PROTECTED]
.........................................
applying technology to transform
.........................................
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
