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

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