At 02:25 PM 11-10-01 +0200, you wrote:

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

Select CODE, SUM(AMOUNT)
FROM ITEMS
GROUP BY CODE

I don't think you can have an updateable view that is based on an aggregation.

regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________