Gerhardus Geldenhuis schrieb:

>
> 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.

For a total per item:
SELECT SUM ( AMOUNT ), CODE FROM ITEMS
GROUP BY CODE

If you want to distinguish betwen in and out:
SELECT SUM ( AMOUNT ), CODE, BOOKTYPE FROM ITEMS
GROUP BY CODE, BOOKTYPE

Luc.