GROUP_CONCAT() produces fatal error in MySQL 4.1.12 - time-sensitive issue

[phillip.s.powell]

SELECT s.id, s.student_first_name, s.student_last_name,
IF(s.student_ethnicity_interest_other IS NOT NULL AND
s.student_ethnicity_interest_other != '',
CONCAT(s.student_ethnicity_interest_other, ', ',
e.ethnicity_name),
GROUP_CONCAT(DISTINCT e.ethnicity_name ORDER BY
upper(e.ethnicity_name))
) AS ethnicity_name

FROM student s, ethnicity e, student_ethnicity_interest_assoc seia
WHERE s.id = seia.student_id
AND seia.ethnicity_id = e.id

GROUP BY s.id, s.student_first_name, s.student_last_name,
ethnicity_name
ORDER BY upper(ethnicity_name) ASC

This query produces the following error:

Can't group on 'ethnicity_name' using query: SELECT s.id...

I am trying to prevent this from happening:

ID student_first_name student_last_name ethnicity_name
1 Phil Powell
hispanics
1 Phil Powell
african-americans

I want this instead:

ID student_first_name student_last_name ethnicity_name
1 Phil Powell
hispanics,african-americans

Please help, this is time-sensitive as this is unfortunately live

Phil

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Can't group on 'ethnicity_name' using query: SELECT s.id...

First, you need to specify the table alias in the GROUP BY clause, or else
your usage of ethnicity_name is ambiguous:

GROUP BY s.id, s.student_first_name, s.student_last_name, e.ethnicity_name

Second, you need to remove ethnicity_name from your GROUP BY clause, or else
you it doesn't make sense to use it in the GROUP_CONCAT function. You'll
always get each value of the ethnicity on separate rows anyway. That's how
GROUP BY works.

GROUP BY s.id, s.student_first_name, s.student_last_name

Regards,
Bill K.

[phillip.s.powell]

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Can't group on 'ethnicity_name' using query: SELECT s.id...
First, you need to specify the table alias in the GROUP BY clause, or else
your usage of ethnicity_name is ambiguous:

GROUP BY s.id, s.student_first_name, s.student_last_name, e.ethnicity_name

Second, you need to remove ethnicity_name from your GROUP BY clause, or else
you it doesn't make sense to use it in the GROUP_CONCAT function. You'll
always get each value of the ethnicity on separate rows anyway. That's how
GROUP BY works.

GROUP BY s.id, s.student_first_name, s.student_last_name

That did it! AT LAST!!!!!!!!! Now if only I understood what you did :(
Thanx!!

Phil

Regards,
Bill K.