Description:
I have a table of items which can be in one or two of many categories.
The categories each item is in are stored in item.category1 and item.category2
To retrieve the total number of items in each category, I need to sum the
count of items grouped by category - items are allowed to be counted twice.
How to repeat:
create table items (iid int, category1 char(10), category2 char(10));
mysql> (select count(*) '_items',category1 'category' from items group by category1) union all (select count(*),category2 from items group by category2);
+--------+------------+
| _items | category |
+--------+------------+
| 1 | boo1 |
| 1 | booadu1 |
| 1 | com1 |
| 1 | NULL |
| 1 | antboo1 |
| 1 | com1 |
+--------+------------+
6 rows in set (0.00 sec)
I need to select sum(_items),category from the above, grouped by category - a temp table would do it, but a single statement would be handy.....
Suggested fix:
as a workaround...... (this is ok because the NULL category above is caused by items in only one category, so we don't want to count it anyway)
select if(c1._items,c1._items,0)+if(c2._items,c2._items,0) '_items',
name 'category'
from categories
left join (select count(*) '_items',category1 from items group by category1)
as c1 on categories.name=c1.category1
left join (select count(*) '_items',category2 from items group by category2)
as c2 on categories.name=c2.category2
having _items>0;
+--------+----------+
| _items | category |
+--------+----------+
| 1 | antboo1 |
| 1 | boo1 |
| 1 | booadu1 |
| 2 | com1 |
+--------+----------+
4 rows in set (0.02 sec)
Description: I have a table of items which can be in one or two of many categories. The categories each item is in are stored in item.category1 and item.category2 To retrieve the total number of items in each category, I need to sum the count of items grouped by category - items are allowed to be counted twice. How to repeat: create table items (iid int, category1 char(10), category2 char(10)); mysql> (select count(*) '_items',category1 'category' from items group by category1) union all (select count(*),category2 from items group by category2); +--------+------------+ | _items | category | +--------+------------+ | 1 | boo1 | | 1 | booadu1 | | 1 | com1 | | 1 | NULL | | 1 | antboo1 | | 1 | com1 | +--------+------------+ 6 rows in set (0.00 sec) I need to select sum(_items),category from the above, grouped by category - a temp table would do it, but a single statement would be handy..... Suggested fix: as a workaround...... (this is ok because the NULL category above is caused by items in only one category, so we don't want to count it anyway) select if(c1._items,c1._items,0)+if(c2._items,c2._items,0) '_items', name 'category' from categories left join (select count(*) '_items',category1 from items group by category1) as c1 on categories.name=c1.category1 left join (select count(*) '_items',category2 from items group by category2) as c2 on categories.name=c2.category2 having _items>0; +--------+----------+ | _items | category | +--------+----------+ | 1 | antboo1 | | 1 | boo1 | | 1 | booadu1 | | 2 | com1 | +--------+----------+ 4 rows in set (0.02 sec)