Re: Help : Sum 2 tables based on key from other table - Mailing list pgsql-general

From Ken Tanzer
Subject Re: Help : Sum 2 tables based on key from other table
Date
Msg-id CAD3a31W1t8Yx9c9SNyb5jQ9bjt3kKziHx7JKdKYDTNtWyKb0-A@mail.gmail.com
Whole thread Raw
In response to Re: Help : Sum 2 tables based on key from other table  (Raymond O'Donnell <rod@iol.ie>)
Responses Re: Help : Sum 2 tables based on key from other table
Re: Help : Sum 2 tables based on key from other table
List pgsql-general
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@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
rod@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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: Raymond O'Donnell
Date:
Subject: Re: Help : Sum 2 tables based on key from other table
Next
From: Hengky Liwandouw
Date:
Subject: Re: Help : Sum 2 tables based on key from other table