Обсуждение: Sum 2 tables based on key from other table
Dear Friends,
Please help for the select command, as i had tried many times and always can not display the result as what i want.
I am looking for the solution on google but still can not found the right answer to solve the problem.
I have 3 tables :
Table A
ProductID
ProductName
SupplierID
Table B
ProductID
InitialStock
Table C
ProductID
Date
In
Out
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'
So the result will look like this :
ProductID ProductName SumofIntialStock sum(in-Out)<beginningdate SumofIN SumofOut
xxxx xxxxxxxxxxxxx 99 99 99
99
xxxx xxxxxxxxxxxxx 99 99 99
99
xxxx xxxxxxxxxxxxx 99 99 99
99
xxxx xxxxxxxxxxxxx 99 99 99
99
What command to get result like this ? i have tried crosstab function but not success too :(
Thanks in advance
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.
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 othertable 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Hengky Lie wrote > 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. >> With qry1 as ( select productid, .... ), qry2 as ( select productid, .... ) , qry3 as (select productid, ... ) Select * from qry1 Join qry2 using (productid) Join qry3 using (productid) Three sub-queries, all sharing a common productid column, joined using that column so you only output 1 table result. Re: schema. This is a situation where I've used monthly summary tables that hold the ending balance for each month to minimize the number of table C records that need to be queried. Thinking more it may make more sense to use a window expression to solve the problem. Sum(...) over (partition by productid order by date) + beginning_balance. Both concepts (cte/with and window functions) are well covered in the documentation, both generally and under the SQL command section for SELECT. You may find someone gives you the (some) correct answers but for now I'll leave this as a learning exercise. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Sum-2-tables-based-on-key-from-other-table-tp5778813p5778837.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.