Re: Sum 2 tables based on key from other table

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general