Dear David, Thanks for your reply. Table A is product table contains ProductID, Name, Supplierid etc. Table B is initial stock contains ProductID, Qty Table C is the transaction table contains ProductID, date, in, out, remarks, etc As i use trigger to write to a log file for any INSERT, UPDATE and DELETE to Table A, i think it is better to make other table that keep initial stock for beginning balance (Table B) Table C is to record transaction for each product. Perhaps this is not a good schema. I should learn from all the expert here. And what my problem in detail is : User may want to see stock in certain date range. Lets say user want to see stock from date range 11/1/2013 and 11/18/2013 for supplier code 'XXX' So, for my schema i have to get data from table b (beginning stock onhand), add it with sum from table C for date <11/1/2013. From Here i got total qty for stock before 11/1/2013 After that, i have to calculate stock for the date between 11/1/2013 and 11/18/2013 in table C And what result i want is : Product ID Product Name Beginningbalance Total In Total Out Balance xxxx xxxxxxxxxx xxxx xxx xxx xxx Is it clear for you what i want ? Do you have an ide about the command ? Please give more detail command. I am quite new to postgresql :) On Nov 18, 2013, at 11:52 AM, David Johnston wrote: > Hengky Lie wrote >> 1. I want to select all productID from Table A where supplierID='XXX'. >> >> 2. Based on list from Step.1 : sum the initialstock from Table B >> >> 3. Based on list from Step 1 : Sum (in-out) from Table C where date >> <'BEGINNING DATE' >> >> 4. Based on list from Step 1 : Sum (in) and sum(out) from Table C where >> date between 'BEGINNING DATE' and 'ENDING DATE' > > You have three questions so you'll likely need three queries. You can > combine them for final output if desired. You can use multiple direct > sub-queries or use CTEs/WITH. > > Note that table B seems pointless. All stock should initially be zero and > the first IN record in Table C establishes the initial balance. That said > I've used a similar schema before so having a starting balance column may > have merit. But why not put in on Table A instead? > > David J. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Sum-2-tables-based-on-key-from-other-table-tp5778813p5778820.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
