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

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(at)iol(dot)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(at)iol(dot)ie
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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/
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list to
> learn more about AGENCY or
> follow the discussion.