List:General Discussion« Previous MessageNext Message »
From:Micha Berdichevsky Date:May 8 2005 6:07am
Subject:Count of multiple columns
View as plain text  
I have a table that store different items. For each items, I can attach 
up to 5 different textual categories.
Those categories are free-text, and different columns can have the same 
values (example below).
I am trying to select the count of each of those categories, regardless 
of it's position.
The table looks like:
    ID - int(11) auto_increment,
    Description - varchar(100),
    Cat1 - varchar(30),
    Cat2 - varchar(30),
    Cat3 - varchar(30),
    Cat4 - varchar(30),
    Cat5 - varchar(30) ... (etc).

Sample data may be:
    1, "aaa", "Food", "America", "Cheese", NULL, NULL
    2, "bbb", "Drink", "America", "Wines", NULL, NULL
    3, "ccc", "Wines", "Drink", NULL, NULL, NULL
    4, "ddd", "America", "Food", NULL, NULL

The result I want is
    Food - 2
    America - 3
    Drink - 2
    Wines - 2
    Cheese - 1

Hope you guys can help (BTW, I'm not too happy with the way the table is 
designed, but couldn't think of a better way).
Thanks, Micha.

Count of multiple columnsMicha Berdichevsky8 May
  • Re: Count of multiple columnsmfatene8 May
  • Re: Count of multiple columnsRhino8 May