Michael wrote:
> On Aug 13, 4:23 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 13 Aug, 15:04, Michael <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
>>
>>
>>
>>> Hi group,
>>> I have a table with rows like this
>>> id name category
>>> 1 b 111
>>> 2 b 111;222
>>> 3 a 111;222;333
>>> 4 b 222;333
>>> 5 c 222
>>> 6 b 222;333
>>> now i use
>>> select id, name, count(*) as number from users where (name like '%b%')
>>> group by name
>>> I'getting correct result:
>>> id name category number
>>> 1 b 111 1
>>> 2 b 111;222 1
>>> 4 b 222;333 2
>>> What I try to do is, to count how often is the name in one
>>> category(for "b" and category "222" it would be 3 times and not 2 as
>>> count say), is that possible in one query? Or schoud I make one more
>>> "while" and then count categories? Actually, I did, but it is slow
>>> then
>>> Hope it was clear...
>> This makes no sense at all.
>>
>> 1) The result table that you show could not be produced by the query
>> that you posted.
>> 2) The query you posted would give a count of 4 for name = b
>> 3) If the name is "b", you should use WHERE `name` = 'b' and not (name
>> like '%b%')
>> 4) If a name can have multiple categories associated with it, it shold
>> be shown as 1 record per category and NOT by having lots of categories
>> in a single field. This is a BIG NO-NO.
>
> 1) and 2) O, I'm sorry! My mistake is order by category and not by
> name as above!
> 3) Agree, but it doesn't matter in this question
>
>

Actually, Paul is correct. This table violates first normal form. If
you had your tables structured properly, this would be much easier. For
instance, a third table:

table categories

id category
1 111
2 111
2 222
3 111
3 222
3 333
4 222
4 333
5 222
6 222
6 333

And your query would be much easier:

select name, count(*) as number from users
join categories on users.id = categories.id
where name='b'
group by name

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================