Thanks, Fred.
It works perfectly.
At 01:00 PM 2/11/2003 +0100, Fred van Engen wrote:
>Hello Anya,
>
>On Tue, Feb 11, 2003 at 07:51:00PM +0800, Anya wrote:
>>
>>
>> Dear all,
>> I have a table Kids which has the following fields:
>>
>> kid char(20),
>> favorite_fruits set('apple','pear','orange',....)
>>
>> favorite_fruits has choices of up to 64 items.
>>
>> Now I want to query the count of each fruit appears in the favorite_fruits of the
>kids in the table. Is there anyway to use 'group by' to get the count, such as:
>select count(*) from Kids group by favorite_fruits?
>>
>> But if use above statement, the results will group by 'apple', 'apple, pear',
>'apple, pear, orange' instead of by 'apple', 'pear', 'orange'
>>
>> Or I have to do one by one such as:
>> select count(*) from Kids where favourite_fruits like '%apple%'
>> and loop against the items in the set?
>>
>
>I never worked with sets, but you could try something like this:
>
>select
> sum(sign(find_in_set('apple', favourite_fruits))) as apples,
> sum(sign(find_in_set('pear', favourite_fruits))) as pears
>from Kids;
>
>This would give you all results on a single result row.
>
>
>Regards,
>
>Fred.
>
>--
>Fred van Engen XB Networks B.V.
>email: [EMAIL PROTECTED] Televisieweg 2
>tel: +31 36 5462400 1322 AC Almere
>fax: +31 36 5462424 The Netherlands
>
Anya
<http://www.acmescripts.com/>http://www.acmescripts.com
---------------------------------------------------------------------
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
