Selon Micha Berdichevsky <[EMAIL PROTECTED]>:
> Hi.
> 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.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Hi,
You can try something not optimized (table structure :o)) like that :
create table if not exists categories(ID int auto_increment primary key,
Description varchar(100),
Cat1 varchar(30),
Cat2 varchar(30),
Cat3 varchar(30),
Cat4 varchar(30),
Cat5 varchar(30));
insert into categories (Description,cat1,cat2,cat3,cat4,cat5) values(
"aaa", "Food", "America", "Cheese", NULL, NULL),("bbb", "Drink", "America",
"Wines", NULL, NULL),
("ccc", "Wines", "Drink", NULL, NULL, NULL),
("ddd", "America", "Food", NULL, NULL,NULL);
select * from categories;
mysql> select res.val,count(res.val) from (select cat1 as val from categories
union all select cat2 as val from categories) res
group by val;
+---------+----------------+
| val | count(res.val) |
+---------+----------------+
| America | 3 |
| Drink | 2 |
| Food | 2 |
| Wines | 1 |
+---------+----------------+
4 rows in set (0.00 sec)
Now just add the "union all" you need.
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
