From: | Raymond O'Donnell <rod(at)iol(dot)ie> |
---|---|
To: | Hengky Liwandouw <hengkyliwandouw(at)gmail(dot)com>, PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Help : Sum 2 tables based on key from other table |
Date: | 2013-11-18 08:47:03 |
Message-ID: | 5289D407.2030103@iol.ie |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2013-11-18 09:37:56 | Re: Help : Sum 2 tables based on key from other table |
Previous Message | Robin St.Clair | 2013-11-18 08:42:11 | Re: Help : Sum 2 tables based on key from other table |