a finance stock management FIFO Sql method

1.table
inStore

id,prodno,prodname,spec,amount,price
------------------------------------
1 01 Pen Blue 10 10.5
2 01 Pen Blue 20 10.5
3 01 Pen Blue 12 10.5
4 01 Pen Black 10 10.5
5 01 Pen Red 10 10.5



2.table
outstore
id,prodno,prodname,spec,amount,price
------------------------------------
1 01 Pen Blue 3 10.5
2 01 Pen Blue 4 10.5
3 01 Pen Black 8 10.5



Instore - OutStore and Grouped =
1 01 Pen Blue 35 10.5
1 01 Pen Black 2 10.5
1 01 Pen Red 10 10.5]


-------------------------------------
Use the following query


Select prodno, prodname, spec,sum(amount),max(price)

From

(
SELECT prodno, prodname, spec, amount, price FROM instore

Union ALL

SELECT prodno, prodname, spec, -amount, price FROM outstore

) as Data

Group By

prodno, prodname, spec