Обсуждение: Help : 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

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

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

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

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

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