Help : Sum 2 tables based on key from other table

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 <http://en.wikipedia.org/wiki/Joe_Celko&gt;. He has
published several books, including SQL For Smarties
<http://en.wikipedia.org/wiki/Special:BookSources/9780120887972&gt; and SQL
Puzzles & Answer
<http://en.wikipedia.org/wiki/Special:BookSources/9780123735966&gt;s, 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
<http://en.wikipedia.org/wiki/Special:BookSources/9780123741370&gt;.

This blog <http://joecelkothesqlapprentice.blogspot.co.uk/&gt;(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 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

You could try using common table expressions, which let you build up to
your final result in steps. Some reading:

http://www.postgresql.org/docs/9.3/static/queries-with.html

http://www.chesnok.com/daily/2013/11/12/how-i-write-queries-using-psql-ctes/

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If the tables aren't huge, you're not concerned about optimization, and you
just want to get your numbers, I think something like this would do the
trick. I haven't actually tried it 'cause I didn't have easy access to
your tables:

SELECT
a.product_id,
a.product_name,
b.initial_stock_sum,
c.in_out_sum,
c.in_sum,
c.out_sum
FROM
a
LEFT JOIN
(SELECT
product_id,
SUM(initial_stock) AS initial_stock_sum
FROM b
GROUP BY product_id
) b USING (product_id)
LEFT JOIN
(SELECT
product_id,
sum(CASE WHEN date < 'BEGINNING DATE' THEN in-out ELSE 0 END) AS
in_out_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN in
ELSE 0 END) AS in_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN out
ELSE 0 END) AS out_sum
FROM c
GROUP BY product_id
) c USING (product_id)
WHERE a.supplier_id='XXX';

Cheers,
Ken

On Mon, Nov 18, 2013 at 12:47 AM, Raymond O'Donnell <rod@iol.ie> wrote:

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 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

You could try using common table expressions, which let you build up to
your final result in steps. Some reading:

http://www.postgresql.org/docs/9.3/static/queries-with.html

http://www.chesnok.com/daily/2013/11/12/how-i-write-queries-using-psql-ctes/

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ <http://agency-software.org/&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.

Thanks all for your concern and help.

I have tried David suggestion and it works. As what you all said, there are so many important feature in PostgreSQL. I really have to spend time to study it.

Last time i use Windev to develop front end application, and HyperfileSQL as the database server. This makes me headache :D

Postgre really fast, has excellent feature, clear documentation, has great community and really so many plus point.

Thanks you all.

On Nov 18, 2013, at 4:47 PM, Raymond O'Donnell wrote:

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 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

You could try using common table expressions, which let you build up to
your final result in steps. Some reading:

http://www.postgresql.org/docs/9.3/static/queries-with.html

http://www.chesnok.com/daily/2013/11/12/how-i-write-queries-using-psql-ctes/

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks a lot Ken,

I will try it soon.

But when the table becomes huge (how big 'huge' in postgres ?), how to optimize such command ?

I have index on all important field like date, productid, supplierid, customerid and so on

Optimization is really an important thing as i plan to keep all transaction data as long as possible.

On Nov 18, 2013, at 5:37 PM, Ken Tanzer wrote:

In general, when I have to handle Ledger type data (which this problem
is), I tend to hold data in 3 tables

1. Master Ledger ( Product ID, Name, etc)
2. Master Ledger Balances(Product ID, Fiscal_Year, Opening Balance,
Net_Transaction_P1, Net_Transaction_P2, ... etc)
3. Master Ledger Transactions(Product_ID, (Fiscal_Year), Date,
Amount......)

I use Triggers and Stored Procedures to maintain consistency. This
allows you to quickly navigate across the population of your data and
drill down to the detailed transaction when required.

Careful manipulation of the Master Ledger Balances table lets you
retrieve multiple different kinds of information at a single pass, ie
This Year To Date Actual, Last Year To Date Actual and Budget This Year
To Date. I usually create functions/SPs to do this even more rapidly.

If you have many bulk updates, it can be better to drop all indices
prior to updating and then rebuilding them.

Robin St.Clair

On 2013-11-18 04:37, Ken Tanzer wrote:

If the tables aren't huge, you're not concerned about optimization,
and you just want to get your numbers, I think something like this
would do the trick. I haven't actually tried it 'cause I didn't have
easy access to your tables:

SELECT
a.product_id,
a.product_name,
b.initial_stock_sum,
c.in_out_sum,
c.in_sum,
c.out_sum
FROM
a
LEFT JOIN
(SELECT
product_id,
SUM(initial_stock) AS initial_stock_sum
FROM b
GROUP BY product_id
) b USING (product_id)
LEFT JOIN
(SELECT
product_id,
sum(CASE WHEN date < 'BEGINNING DATE' THEN in-out ELSE 0 END) AS
in_out_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN
in ELSE 0 END) AS in_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN
out ELSE 0 END) AS out_sum
FROM c
GROUP BY product_id
) c USING (product_id)
WHERE a.supplier_id='XXX';

Cheers,
Ken

I'm a big fan of using LATERAL joins (9.3+) for this use case.