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