473,573 Members | 2,865 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

SELECT s.id, s.student_first _name, s.student_last_ name,
IF(s.student_et hnicity_interes t_other IS NOT NULL AND
s.student_ethni city_interest_o ther != '',
CONCAT(s.studen t_ethnicity_int erest_other, ', ',
e.ethnicity_nam e),
GROUP_CONCAT(DI STINCT e.ethnicity_nam e ORDER BY
upper(e.ethnici ty_name))
) AS ethnicity_name

FROM student s, ethnicity e, student_ethnici ty_interest_ass oc 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_n ame student_last_na me ethnicity_name
1 Phil Powell
hispanics
1 Phil Powell
african-americans

I want this instead:

ID student_first_n ame student_last_na me ethnicity_name
1 Phil Powell
hispanics,afric an-americans

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

Phil

Mar 3 '06 #1
2 1919
<ph************ **@gmail.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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_nam e

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.
Mar 4 '06 #2

Bill Karwin wrote:
<ph************ **@gmail.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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_nam e

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.


Mar 4 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
9969
by: BT | last post by:
Hi .. a newbie question. I get the following error: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/index.php on line 3. I have the following RPMS installed on RH7.2 MySQL-client-4.0.14-0 I would really appreciate it. MySQL-shared-4.0.14-0 MySQL-server-4.0.14-0 MySQL-devel-4.0.14-0
4
15337
by: gc | last post by:
I'm a PHP and MySQL newbie. I have a feeling a lot of you may have seen this before. I'm teaching myself PHP/MySQL and trying to setup a guestbook. I'm running latest versions of Apache, PHP and MySql on WIN 2000. PHP is installed from the zip file. When serving php docs (code snippets below) I get the messages: "Fatal error: Call to...
1
13654
by: Mike | last post by:
Last weekend I decided to install Apache 2.0.53-win32-x86-no_ssl PHP 5.0.3 Smarty 2.6.7 MySQL essential-4.1.10-win32 I have Apache up (Port 80 blocked at the router and firewall!) and I have got Smarty working. I haven't got around to installing MySQL. I am now in chapter 8 'Error and Exception Handling' of
6
1545
by: bettina | last post by:
I want to list all the countries (147 in total) per continent with the quantity of beercoasters that each country has. I get the following message: Fatal error: Maximum execution time of 30 seconds exceeded in C:\wampp2\htdocs\main1.php on line 88 Here is the code...perhaps the loop is too big? ......
1
18052
by: drewmania001 | last post by:
i've read various info on the web including the following http://php.mirrors.ilisys.com.au/manual/en/ref.mysqli.php mySQL Version 4.1.16 PHP Version 5.1.2 with Zend Engine v2.1.0 OS Windows NT SERVER(2003) 5.2 build 3790 see http://drew.bounceme.net/phpinfo.php for more details
2
1701
by: phillip.s.powell | last post by:
Here are my results using MySQL 4.1.12 and I honestly don't think this is right, but can't figure out what to do: mysql> select id, group_concat(DISTINCT school_enrollment_time_name SEPARATOR ',') as school_enrollment_time_display from school_enrollment_time group by id; +----+--------------------------------+ | id |...
0
1220
by: phillip.s.powell | last post by:
$if1 = "IF(s.student_ethnicity_interest_other IS NOT NULL AND s.student_ethnicity_interest_other != '', " . " CONCAT(s.student_ethnicity_interest_other, ', ', GROUP_CONCAT(e.ethnicity_name ORDER BY upper(e.ethnicity_name))), " . ' GROUP_CONCAT(e.ethnicity_name ORDER BY upper(e.ethnicity_name))' . " ) AS ethnicity_name \n\n"; This...
0
3316
by: gmorris | last post by:
I am attempting to pull data from multiple tables into a resultset that I can use as an Excel spreadsheet for reporting. Here is my query so far: SELECT b.bug_id,p.realname as 'Assigned' ,bug_status,b.resolution,b.short_desc,l.thetext as 'Description/Comments',b.creation_ts as 'Opened',b.delta_ts as 'Last Update' FROM bugs b, longdescs...
49
16265
by: Julian Bowler | last post by:
Please can some one help with my problem; I am running Apache web server on my lap top, plus a mysql server and php. The version of php currently installed is 5.2.5. when i write some php to connect to my mysql database server instead of it connecting i get : Fatal error: Call to undefined function mysql_connect() in C:\Program Files\Apache...
1
5746
bilibytes
by: bilibytes | last post by:
Hi, I'm wondering how to achieve the following: well i don't know if you guys like wine but I have to explain a very little on wine to explain my example: Wine is made from grape and a wine bottle may be made from different sorts of grape. So when you have a bottle it usually says: 10% of shyra, 50% of cabernet and 40% of merlot meaning...
0
7755
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8190
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6385
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5571
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5281
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3722
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1284
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.