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
---------------------------------------------------------------------
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
