WITH ROLLUP value inheritance
I'll start off by giving the commands to replicate my problem:
drop table if exists items;
drop table if exists sales;
create table items (`item_number` int,`description` varchar(20),primary key(`item_number`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create table sales (`invoice` int, `item_number` int,`qty` int,key (`invoice`, `item_number`), key(`item_number`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into items values (1, 'ballpoint pen'), (2, '\#2 pencil');
insert into sales values (123, 1, 5), (123, 2, 3), (124, 1, 2), (125, 2, 2);
select sales.item_number, items.description, sum(sales.qty) from sales inner join items on (sales.item_number = items.item_number) group by sales.item_number with rollup;
gives me:
+-------------+---------------+----------------+
| item_number | description | sum(sales.qty) |
+-------------+---------------+----------------+
| 1 | ballpoint pen | 7 |
| 2 | #2 pencil | 5 |
| NULL | #2 pencil | 12 |
+-------------+---------------+----------------+
3 rows in set (0.00 sec)
The desired result is:
+-------------+---------------+----------------+
| item_number | description | sum(sales.qty) |
+-------------+---------------+----------------+
| 1 | ballpoint pen | 7 |
| 2 | #2 pencil | 5 |
| NULL | NULL | 12 |
+-------------+---------------+----------------+
with the description of the summary being NULL.
It appears that the description is being inherited by the last not null value, which is not desirable. It is essential to have both the item_number and the description in the query and to have them separate. Note that item_number is the primary key and thus being unique. The description may or may not be unique. The example above is a more simple query than what I am actually trying to accomplish. The real query involves item classes and subclasses and to display the class name and subclass name, but group by class id and subclass id. If anyone knows of a way to accomplish the desired result, much thanks to you in advance and much apologies if this problem has been solved.