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 <r...@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 > r...@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/ > ken.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing list to > learn more about AGENCY or > follow the discussion.
