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