Re: Help : Sum 2 tables based on key from other table - Mailing list pgsql-general

From Robin St.Clair
Subject Re: Help : Sum 2 tables based on key from other table
Date
Msg-id BLU0-SMTP3761D25BA8AEB2097A7879CE2E40@phx.gbl
Whole thread Raw
In response to Help : Sum 2 tables based on key from other table  (Hengky Liwandouw <hengkyliwandouw@gmail.com>)
List pgsql-general
Hi

For decades, this type of problem has been the meat and vegetables of discussions about SQL programming and design.

One writer on this subject has stood out, thanks to his mental clarity and ability to set out complicated concepts in a readily comprehensible manner.

His name is Joe Celko. He has published several books, including SQL For Smarties and  SQL Puzzles & Answers, you may even find them in .pdf format online.

Read some of what Joe has written and you will find answers to this sort of problem.

Incidentally, I can't remember a problem like this since we stored data on open reel tape systems.  We used to set multiway masterfile update problems for first year Information System students, before they had widespread access to ISAM and database hosted exercises. What you have is a 'batch' system, you might find it worthwhile rolling (virtually) A & B together and then grouping C. The trick will be in the way you handle grouping, Joe Cleko has an entire book on this subject - Thinking in Sets: Auxiliary, Temporal & Virtual Tables in SQL.

This blog (based on his solutions) is worth a look.

Robin St.Clair




On 18/11/2013 02:16, Hengky Liwandouw wrote:
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 AProductID ProductNameSupplierID

Table BProductIDInitialStock

Table CProductIDDateInOut

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




pgsql-general by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: freeze cannot be finished
Next
From: Raymond O'Donnell
Date:
Subject: Re: Help : Sum 2 tables based on key from other table