Thread: Help : Sum 2 tables based on key from other table

Help : Sum 2 tables based on key from other table

From
Hengky Liwandouw
Date:
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




Re: Help : Sum 2 tables based on key from other table

From
Robin St.Clair
Date:
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




Re: Help : Sum 2 tables based on key from other table

From
Raymond O'Donnell
Date:
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


Re: Help : Sum 2 tables based on key from other table

From
Ken Tanzer
Date:
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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Help : Sum 2 tables based on key from other table

From
Hengky Liwandouw
Date:
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
reallyhave 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



Re: Help : Sum 2 tables based on key from other table

From
Hengky Liwandouw
Date:
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:

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Help : Sum 2 tables based on key from other table

From
Robin St.Clair
Date:
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 18/11/2013 10:04, Hengky Liwandouw wrote:
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:

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


Re: Help : Sum 2 tables based on key from other table

From
Elliot
Date:
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.