Hi
I have a table with the following structure
CREATE TABLE ITEMS
(
ID INTEGER NOT NULL,
AMOUNT INTEGER,
CODE VARCHAR(10),
BOOKTYPE CHAR(5),

CONSTRAINT PK_ITEMS PRIMARY KEY (ID)
)
Typical data would look like this
1 10 A4 IN
2 20 A3 IN
3 -1 A4 OUT
4 2 A3 IN
5 -8 A3 OUT
etc.
Strictly spoken the booktype field is not nessesary.

The table basically keeps track of stock booked in and booked out.
Now I can write a select statement which gives me the amount available for a
specific item and I can write a select statement giving me a list of all the
different items.

select sum(amount) from items where code='A4'
select distinct code from items

I would like to know how can I combine these two statements to give me a
table with each item and its quantity. Can it be done with a view or must I
use a stored procedure. With a view it could, if posible, have a trigger
which adds a item, to subtract stock, to the main table.

Groete
Gerhardus