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

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.

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

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

On Aug 13, 4:48 pm, Michael <fj4sgcv5hy6d...@temporaryinbox.com>
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

I'm sorry, all! Select statement in my question shoud be like this

select id, name, count(*) as number from users where (name like '%b%')
group by category

On Aug 13, 5:12 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> 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.
> jstuck...@attglobal.net
> ==================

Unfortunally it is not my table, i can't change it

In my question i did mistake in select, it should be

select id, name, count(*) as number from users where (name like '%b%')
group by category

Michael wrote:
> On Aug 13, 5:12 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> 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.
>> jstuck...@attglobal.net
>> ==================
>
> Unfortunally it is not my table, i can't change it
>
> In my question i did mistake in select, it should be
>
> select id, name, count(*) as number from users where (name like '%b%')
> group by category
>

Hmmm, I'm not sure it's possible here. The problem is that 'category'
contains multiple values, so when you group by it, you get just what you
see - as far as MySQL is concerned, '111;222' is different from '222;333'.

You would need to group on a subset of the category column, but since
your '222' is in different places, I'm not sure how that can be done.

That's why normalizing a database is so important - it takes care of
problem such as this.

You may have to just go to a higher level language like PHP, Perl, etc.
to do the final work. Or get whomever designed the database to
normalize it properly (google for "Database Normalization").

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

On Aug 13, 7:41 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Michael wrote:
> > On Aug 13, 5:12 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> 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.
> >> jstuck...@attglobal.net
> >> ==================
>
> > Unfortunally it is not my table, i can't change it
>
> > In my question i did mistake in select, it should be
>
> > select id, name, count(*) as number from users where (name like '%b%')
> > group by category
>
> Hmmm, I'm not sure it's possible here. The problem is that 'category'
> contains multiple values, so when you group by it, you get just what you
> see - as far as MySQL is concerned, '111;222' is different from '222;333'.
>
> You would need to group on a subset of the category column, but since
> your '222' is in different places, I'm not sure how that can be done.
>
> That's why normalizing a database is so important - it takes care of
> problem such as this.
>
> You may have to just go to a higher level language like PHP, Perl, etc.
> to do the final work. Or get whomever designed the database to
> normalize it properly (google for "Database Normalization").
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Now I know it's impossible :L
Anyway, thanks to all for your help!

On 15 Aug, 15:29, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
> On Aug 13, 7:41 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
>
>
>
>
> > Michael wrote:
> > > On Aug 13, 5:12 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> > >> 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.
> > >> jstuck...@attglobal.net
> > >> ==================
>
> > > Unfortunally it is not my table, i can't change it
>
> > > In my question i did mistake in select, it should be
>
> > > select id, name, count(*) as number from users where (name like '%b%')
> > > group by category
>
> > Hmmm, I'm not sure it's possible here. The problem is that 'category'
> > contains multiple values, so when you group by it, you get just what you
> > see - as far as MySQL is concerned, '111;222' is different from '222;333'.
>
> > You would need to group on a subset of the category column, but since
> > your '222' is in different places, I'm not sure how that can be done.
>
> > That's why normalizing a database is so important - it takes care of
> > problem such as this.
>
> > You may have to just go to a higher level language like PHP, Perl, etc.
> > to do the final work. Or get whomever designed the database to
> > normalize it properly (google for "Database Normalization").
>
> > --
> > ==================
> > Remove the "x" from my email address
> > Jerry Stuckle
> > JDS Computer Training Corp.
> > jstuck...@attglobal.net
> > ==================
>
> Now I know it's impossible :L
> Anyway, thanks to all for your help!- Hide quoted text -
>
> - Show quoted text -

Actually it "MAY" be possible to do something in this case, but you
will need to be a bit more helpful.

I said "The result table that you show could not be produced by the
query that you posted."
You replied: "My mistake is order by category and not by name as
above!"
The is no ORDER BY name in the original query! Even assuming you meant
GROUP BY, the revised query could still not have produced the output
you listed.

Rather than making up a query, please show us the ACTUAL query that
gave you the correct result! Had you done that in the first place,
there is no way that you could have swapped the names.

On Aug 15, 5:57 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 15 Aug, 15:29, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
>
>
>
> > On Aug 13, 7:41 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
> > > Michael wrote:
> > > > On Aug 13, 5:12 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> > > >> 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.
> > > >> jstuck...@attglobal.net
> > > >> ==================
>
> > > > Unfortunally it is not my table, i can't change it
>
> > > > In my question i did mistake in select, it should be
>
> > > > select id, name, count(*) as number from users where (name like '%b%')
> > > > group by category
>
> > > Hmmm, I'm not sure it's possible here. The problem is that 'category'
> > > contains multiple values, so when you group by it, you get just what you
> > > see - as far as MySQL is concerned, '111;222' is different from '222;333'.
>
> > > You would need to group on a subset of the category column, but since
> > > your '222' is in different places, I'm not sure how that can be done.
>
> > > That's why normalizing a database is so important - it takes care of
> > > problem such as this.
>
> > > You may have to just go to a higher level language like PHP, Perl, etc.
> > > to do the final work. Or get whomever designed the database to
> > > normalize it properly (google for "Database Normalization").
>
> > > --
> > > ==================
> > > Remove the "x" from my email address
> > > Jerry Stuckle
> > > JDS Computer Training Corp.
> > > jstuck...@attglobal.net
> > > ==================
>
> > Now I know it's impossible :L
> > Anyway, thanks to all for your help!- Hide quoted text -
>
> > - Show quoted text -
>
> Actually it "MAY" be possible to do something in this case, but you
> will need to be a bit more helpful.
>
> I said "The result table that you show could not be produced by the
> query that you posted."
> You replied: "My mistake is order by category and not by name as
> above!"
> The is no ORDER BY name in the original query! Even assuming you meant
> GROUP BY, the revised query could still not have produced the output
> you listed.

O my... one more mistake. Of course it is GROUP BY category

select id, name, count(*) as number from users where (name like '%b%')
group by category

>
> Rather than making up a query, please show us the ACTUAL query that
> gave you the correct result! Had you done that in the first place,
> there is no way that you could have swapped the names.

well, At the moment I take all categories, there are in table but
without ";",
so I have then fetched 10-20 categories.
Then make [php] an
while
{
// select counting categoriese
"select count(id) where name="b" and category like '%
$one_of_fetched_above%'"
}

this works fine but too slow, there are 100000 rows in that table and
it grows daily.