MySQL Forums
Forum List  »  MyISAM

WITH ROLLUP value inheritance
Posted by: Daniel Schaefer
Date: August 22, 2007 11:27AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
WITH ROLLUP value inheritance
3306
August 22, 2007 11:27AM
2589
August 22, 2007 02:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.