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.

Ciao!

On 15 Aug, 16:14, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
> 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.
>
> Ciao!- Hide quoted text -
>
> - Show quoted text -

And I STILL say that there is NO WAY that the revised query could EVER
create the result data that you posted!

I said "Even assuming you meant GROUP BY, the revised query could
still not have produced the output you listed."

And you ignored all that!

POST THE QUERY THAT YOU RAN TO GET THE RESULT TABLE THAT YOU POSTED.

The result table did NOT come from the query:
select id, name, count(*) as number from users where (name like '%b
%')
group by category

So show us the query that you REALLY used!

On Aug 15, 6:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 15 Aug, 16:14, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
>
>
>
> > 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.
>
> > Ciao!- Hide quoted text -
>
> > - Show quoted text -
>
> And I STILL say that there is NO WAY that the revised query could EVER
> create the result data that you posted!
>
> I said "Even assuming you meant GROUP BY, the revised query could
> still not have produced the output you listed."
>
> And you ignored all that!
>
> POST THE QUERY THAT YOU RAN TO GET THE RESULT TABLE THAT YOU POSTED.
>
> The result table did NOT come from the query:
> select id, name, count(*) as number from users where (name like '%b
> %')
> group by category
>
> So show us the query that you REALLY used!
Hi,
this is the query

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

this is the result
id name category number
1 b 111 1
3 b 111;222;333 1
4 b 222;333 2

As Jerry Stuckle said, it is the problem
that '111;222' is different from '222;333'.

Vv_vV wrote:
> On Aug 15, 6:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 15 Aug, 16:14, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
>>
>>
>>
>>> 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.
>>> Ciao!- Hide quoted text -
>>> - Show quoted text -
>> And I STILL say that there is NO WAY that the revised query could EVER
>> create the result data that you posted!
>>
>> I said "Even assuming you meant GROUP BY, the revised query could
>> still not have produced the output you listed."
>>
>> And you ignored all that!
>>
>> POST THE QUERY THAT YOU RAN TO GET THE RESULT TABLE THAT YOU POSTED.
>>
>> The result table did NOT come from the query:
>> select id, name, count(*) as number from users where (name like '%b
>> %')
>> group by category
>>
>> So show us the query that you REALLY used!
> Hi,
> this is the query
>
> select id, name,category, count(*) as number from test where (name
> like '%b%')
> group by category
>
> this is the result
> id name category number
> 1 b 111 1
> 3 b 111;222;333 1
> 4 b 222;333 2
>
> As Jerry Stuckle said, it is the problem
> that '111;222' is different from '222;333'.
>

Additionally, it will be very slow because MySQL cannot use an index on
category. It will have to do a table search for every request, and that
will get slower as your table grows.

To top things off, as it does the table search, it has to compare the
LIKE on each row - effectively a partial string compare, which slows
things down even more.

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

On 15 ago, 13:57, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Vv_vV wrote:
> > On Aug 15, 6:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> >> On 15 Aug, 16:14, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
>
> >>> 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.
> >>> Ciao!- Hide quoted text -
> >>> - Show quoted text -
> >> And I STILL say that there is NO WAY that the revised query could EVER
> >> create the result data that you posted!
>
> >> I said "Even assuming you meant GROUP BY, the revised query could
> >> still not have produced the output you listed."
>
> >> And you ignored all that!
>
> >> POST THE QUERY THAT YOU RAN TO GET THE RESULT TABLE THAT YOU POSTED.
>
> >> The result table did NOT come from the query:
> >> select id, name, count(*) as number from users where (name like '%b
> >> %')
> >> group by category
>
> >> So show us the query that you REALLY used!
> > Hi,
> > this is the query
>
> > select id, name,category, count(*) as number from test where (name
> > like '%b%')
> > group by category
>
> > this is the result
> > id name category number
> > 1 b 111 1
> > 3 b 111;222;333 1
> > 4 b 222;333 2
>
> > As Jerry Stuckle said, it is the problem
> > that '111;222' is different from '222;333'.
>
> Additionally, it will be very slow because MySQL cannot use an index on
> category. It will have to do a table search for every request, and that
> will get slower as your table grows.
>
> To top things off, as it does the table search, it has to compare the
> LIKE on each row - effectively a partial string compare, which slows
> things down even more.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

I think you might build a layer over what you've got to fix this.
The objective is to create a CATEGORY tabe as Jerry said.
This might be done by a Stored Procedure and an adicional column in
your original table.
This is, add a BIT column indicating if that row was or not parsed (1
or 0).
The Stored Procedure would also be triggered on an Insert, Update or
Delete.
The SP should use a cursor to go through each unparsed row (0), and
usind the Replace (to convert ; to , ) and PREPARE a query using the
ELT() function to Insert ID and Category on a Category table for each
item in your original table 'till null found.
That way you'll have your original table, plus the auxiliary table
formated for a fast query.
Carlos Troncoso Phillips

On 15 Aug., 22:36, Carlos Troncoso <schmic...@gmail.com> wrote:
> On 15 ago, 13:57, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
>
>
> > Vv_vV wrote:
> > > On Aug 15, 6:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> > >> On 15 Aug, 16:14, Vv_vV <fj4sgcv5hy6d...@temporaryinbox.com> wrote:
>
> > >>> 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.
> > >>> Ciao!- Hide quoted text -
> > >>> - Show quoted text -
> > >> And I STILL say that there is NO WAY that the revised query could EVER
> > >> create the result data that you posted!
>
> > >> I said "Even assuming you meant GROUP BY, the revised query could
> > >> still not have produced the output you listed."
>
> > >> And you ignored all that!
>
> > >> POST THE QUERY THAT YOU RAN TO GET THE RESULT TABLE THAT YOU POSTED.
>
> > >> The result table did NOT come from the query:
> > >> select id, name, count(*) as number from users where (name like '%b
> > >> %')
> > >> group by category
>
> > >> So show us the query that you REALLY used!
> > > Hi,
> > > this is the query
>
> > > select id, name,category, count(*) as number from test where (name
> > > like '%b%')
> > > group by category
>
> > > this is the result
> > > id name category number
> > > 1 b 111 1
> > > 3 b 111;222;333 1
> > > 4 b 222;333 2
>
> > > As Jerry Stuckle said, it is the problem
> > > that '111;222' is different from '222;333'.
>
> > Additionally, it will be very slow because MySQL cannot use an index on
> > category. It will have to do a table search for every request, and that
> > will get slower as your table grows.
>
> > To top things off, as it does the table search, it has to compare the
> > LIKE on each row - effectively a partial string compare, which slows
> > things down even more.
>
> > --
> > ==================
> > Remove the "x" from my email address
> > Jerry Stuckle
> > JDS Computer Training Corp.
> > jstuck...@attglobal.net
> > ==================
>
> I think you might build a layer over what you've got to fix this.
> The objective is to create a CATEGORY tabe as Jerry said.
> This might be done by a Stored Procedure and an adicional column in
> your original table.
> This is, add a BIT column indicating if that row was or not parsed (1
> or 0).
> The Stored Procedure would also be triggered on an Insert, Update or
> Delete.
> The SP should use a cursor to go through each unparsed row (0), and
> usind the Replace (to convert ; to , ) and PREPARE a query using the
> ELT() function to Insert ID and Category on a Category table for each
> item in your original table 'till null found.
> That way you'll have your original table, plus the auxiliary table
> formated for a fast query.
> Carlos Troncoso Phillips

Hi,
thank you for help! It sounds interesting. I've never wrote an SP, so
it sounds for work to me.

Best Regards!