RE: Sum function question

2002-02-27 Thread Roger Karnouk 

It can be done but it involves quite a few statements
and the use of temporary variables and a temporary table.

assuming that your table is defined as follows

CREATE TABLE `tble` (
  `keyf` char(1) default NULL,
  `Field1` char(8) default NULL
)

do this:

set @k='';

set @field='';

create temporary table tmp
(temp1 mediumtext,temp2 mediumtext,keyf char(1),field1 mediumtext);

insert into tmp
select if(@k=keyf, @field:=
CONCAT_WS(',',@field,field1),@field:=field1),if(@k!=keyf,@k:=keyf,@k),
@k,@field from tble;

select keyf as 'key', max(field1) as 'field-1'
from tmp
group by keyf;

-Original Message-
From: Javier [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 27, 2002 8:11 AM
To: [EMAIL PROTECTED]
Subject: Sum function question


 Hi, I have a table like this

   Key   Field-1
   A   string1
   A   string2
   A   string3
   B   string1
   B   string 2

 I want to create a query to get the following result (only one line by key
field):

   A  - string1,string2,string3
   B  - string1,string2

 I try to use the Sum  function but it only works on numbers, ( the number
of occurrences of  key field, is unknown)

 Thanks in advance for any advice that you might have.

 Javier Diaz
IT Developer


 - Scanned for all known viruses by Messagelabs --

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Sum function question

2002-02-27 Thread DL Neil 

Hi Javier,

> Hi, I have a table like this
> 
>Key   Field-1
>A   string1
>A   string2
>A   string3
>B   string1
>B   string 2
> 
>  I want to create a query to get the following result (only one line by key
> field):
> 
>A  - string1,string2,string3
>B  - string1,string2
> 
>  I try to use the Sum  function but it only works on numbers, ( the number
> of occurrences of  key field, is unknown)


Relational queries are performed on tables. The result of such a query will also be a 
table.
AFAIK there is no serialisation facility - you would need to post-process using 
another tool/language.

Regards,
=dn



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Sum function question

2002-02-27 Thread Javier 

 Hi, I have a table like this

   Key   Field-1
   A   string1
   A   string2
   A   string3
   B   string1
   B   string 2

 I want to create a query to get the following result (only one line by key
field):

   A  - string1,string2,string3
   B  - string1,string2

 I try to use the Sum  function but it only works on numbers, ( the number
of occurrences of  key field, is unknown)

 Thanks in advance for any advice that you might have.

 Javier Diaz
IT Developer


 - Scanned for all known viruses by Messagelabs --

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Sum function question

2002-02-20 Thread Javier 


- Original Message -
From: Javier <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 20, 2002 2:47 PM
Subject: Sum function question


> Hi, i have a table like this
>
> Key   Field-1
> A   string1
> A   string2
> A   string3
> B   string1
> B   string 2
>
> I want to create a query to get the following result (only one line by key
> field):
>
> A  - string1,string2,string3
> B  - string1,string2
>
> I try to use the Sum  function but it only works on numbers, ( the number
of
> occurrences of  key field, is unknown)
>
> Thanks in advance for any advice that you might have.
>
> Javier
>


 - Scanned for all known viruses by Messagelabs --

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Sum function question

2002-02-20 Thread Javier 

Hi, i have a table like this

Key   Field-1
A   string1
A   string2
A   string3
B   string1
B   string 2

I want to create a query to get the following result (only one line by key
field):

A  - string1,string2,string3
B  - string1,string2

I try to use the Sum  function but it only works on numbers, ( the number of
occurrences of  key field, is unknown)

Thanks in advance for any advice that you might have.

Javier


 - Scanned for all known viruses by Messagelabs --

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php