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!