Thread: sum of until (running balance) and sum of over date range in the same query

Hi everyone,

I've been working and thinking of a way to get this data that I need, 
but I can't find a way to get it in one query.

these are simplified tables and the query below that I've tried to make:


CREATE TABLE dept (  dept_id character(22) NOT NULL,  name character varying(30) NOT NULL,  "number" character
varying(5)
)

CREATE TABLE subdept
(  subdept_id character(22) NOT NULL,  name character varying(30) NOT NULL,  "number" character varying(5),  dept_id
character(22)NOT NULL
 
)

CREATE TABLE item
(  item_id character(22) NOT NULL,  version integer NOT NULL,  description character varying(40) NOT NULL,  dept_id
character(22), subdept_id character(22),  expense_acct character(22),  income_acct character(22),  asset_acct
character(22), sell_size character varying(8) NOT NULL,  purch_size character varying(8) NOT NULL
 
)

CREATE TABLE item_size
(  item_id character(22) NOT NULL,  seq_num integer NOT NULL,  name character varying(8) NOT NULL,  qty numeric(18,4)
NOTNULL,  weight numeric(18,4) NOT NULL
 
)

CREATE OR REPLACE VIEW view_item_change AS SELECT date_part('year'::text, item_change.change_date) AS year,
date_part('month'::text,item_change.change_date) AS month,    date_part('week'::text, item_change.change_date) AS week,
  date_part('quarter'::text, item_change.change_date) AS quarter,    date_part('dow'::text, item_change.change_date) AS
dow,
 
item_change.item_id,    item_change.size_name, item_change.store_id, item_change.change_date,    item_change.on_hand,
item_change.total_cost,item_change.on_order,    item_change.sold_qty, item_change.sold_cost, item_change.sold_price,
item_change.recv_qty,item_change.recv_cost, item_change.adj_qty,    item_change.adj_cost   FROM item_change;
 


select
year*100+month as yearmonth,
(select number from item_plu where item_plu.item_id = 
view_item_change.item_id) as itmNumber,
description,
sum(view_item_change.sold_qty /   item_size.qty)::numeric(18,2) as qty,  
-- qty sold during grouped time
sum(view_item_change.sold_cost)::numeric(18,2) as cost,  -- total cost 
of those sold - COGS
sum(view_item_change.sold_price)::numeric(18,2) as sales,  -- total sold 
value
sum(amount) -- amount sold over the grouped period
from ((view_item_change
join item on view_item_change.item_id = item.item_id)
join item_size on item_size.item_id = view_item_change.item_id and 
item_size.name = item.sell_size)
join dept on item.dept_id = dept.dept_id
join subdept on item.subdept_id = subdept.subdept_id
where view_item_change.change_date >= '2012-01-01'
and item.dept_id = (select dept_id from dept where name = 'Oil')
group by year,month,  view_item_change.item_id, description
order by year,month, itmNumber


Note the comments in the query.  The on_hand column has an entry for 
each day that an item qty changes, so to get a current On Hand, I do a 
sum(amount) where change_date <= 'date'.

What I want is a result set grouped by year/quarter/month/week by item, 
showing on hand at end of that time period and the sum of the amount 
sold during that time.  Is it possible to get this data in one query?  
The complication is that the sold qty is over the group, while On Hand 
is a running balance.

Thanks,
Mark

M. D. wrote
> What I want is a result set grouped by year/quarter/month/week by item, 
> showing on hand at end of that time period and the sum of the amount 
> sold during that time.  Is it possible to get this data in one query?  
> The complication is that the sold qty is over the group, while On Hand 
> is a running balance.

So my eyes glazed over scanning your post but I notice you are not using
Window Functions.

http://www.postgresql.org/docs/9.3/interactive/tutorial-window.html
http://www.postgresql.org/docs/9.3/interactive/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

You need to learn about this concept as it likely will readily solve your
problem.

SELECT day, month, year, sum(sold_qty) AS qty_sold_day_groupall
, sum(sum(sold_qty)) OVER (PARTITION BY day) AS qty_sold_day
, sum(sold_qty) OVER (PARTITION BY month) AS qty_sold_month 
, sum(sold_qty) OVER (PARTITION BY year) qty_sold_year
, sum(sold_qty) OVER (PARTITION BY year ORDER BY day) AS qty_sold_ytd
FROM ... GROUP BY day, month, year ORDER BY day

Note the double-sum { sum(sum(...)) OVER () } is needed due to the GROUP BY. 
If you want to use the original data you can omit the GROUP BY and the inner
sum() invocation.

qty_sold_day_groupall == qty_sold_day
qty_sold_month & qty_sold_year will repeat (the same same exact value for
every day in the corresponding month/year).

qty_sold_ytd: this is special because of the ORDER BY.  Only the rows prior
to and including the current day are considered (for the other columns,
lacking the ORDER BY, every row in the partition is considered) so it
effectively becomes a running total of all prior days plus the current day.

These are well documented and many window-specific functions exists as well
as being able to use any normal aggregate function in a window context. 
They take a while to learn but are extremely powerful/useful.  Performance
can become a factor because unlike normal GROUP BY aggregation every
original row in the source table is output.  In the above example we didn't
want all items to be output so we performed a GROUP BY to aggregate the
items THEN we used windows to perform the separate aggregates in a window
fashion.

An alternative method (or can be used in conjunction) would be to separate
these into multiple sub-queries using CTEs (WITH)

WITH group_items AS (  SELECT day, month, year, sum(sold_qty) AS daily_sale
FROM items ... )
, group_aggs AS ( SELECT day, month, year, daily_sale, sum(daily_sale) OVER
(PARTITION BY month) FROM group_item )

or instead of WINDOW functions you can write additional GROUP BY CTE queries
for the different time-frames

..., month_total AS ( SELECT month, year, sum(daily_sale) FROM group_items
GROUP BY month, year )

and then combine these different CTE queries as you deem appropriate.

http://www.postgresql.org/docs/9.3/interactive/sql-select.html   (the
section for "WITH [RECURSIVE])

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/sum-of-until-running-balance-and-sum-of-over-date-range-in-the-same-query-tp5776209p5776213.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On 10/28/2013 10:17 PM, David Johnston wrote:
> M. D. wrote
>> What I want is a result set grouped by year/quarter/month/week by item,
>> showing on hand at end of that time period and the sum of the amount
>> sold during that time.  Is it possible to get this data in one query?
>> The complication is that the sold qty is over the group, while On Hand
>> is a running balance.
> So my eyes glazed over scanning your post but I notice you are not using
> Window Functions.
>
> http://www.postgresql.org/docs/9.3/interactive/tutorial-window.html
> http://www.postgresql.org/docs/9.3/interactive/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
>
> You need to learn about this concept as it likely will readily solve your
> problem.
>
> SELECT day, month, year, sum(sold_qty) AS qty_sold_day_groupall
> , sum(sum(sold_qty)) OVER (PARTITION BY day) AS qty_sold_day
> , sum(sold_qty) OVER (PARTITION BY month) AS qty_sold_month
> , sum(sold_qty) OVER (PARTITION BY year) qty_sold_year
> , sum(sold_qty) OVER (PARTITION BY year ORDER BY day) AS qty_sold_ytd
> FROM ... GROUP BY day, month, year ORDER BY day
>
> Note the double-sum { sum(sum(...)) OVER () } is needed due to the GROUP BY.
> If you want to use the original data you can omit the GROUP BY and the inner
> sum() invocation.
>
> qty_sold_day_groupall == qty_sold_day
> qty_sold_month & qty_sold_year will repeat (the same same exact value for
> every day in the corresponding month/year).
>
> qty_sold_ytd: this is special because of the ORDER BY.  Only the rows prior
> to and including the current day are considered (for the other columns,
> lacking the ORDER BY, every row in the partition is considered) so it
> effectively becomes a running total of all prior days plus the current day.
>
> These are well documented and many window-specific functions exists as well
> as being able to use any normal aggregate function in a window context.
> They take a while to learn but are extremely powerful/useful.  Performance
> can become a factor because unlike normal GROUP BY aggregation every
> original row in the source table is output.  In the above example we didn't
> want all items to be output so we performed a GROUP BY to aggregate the
> items THEN we used windows to perform the separate aggregates in a window
> fashion.
>
> An alternative method (or can be used in conjunction) would be to separate
> these into multiple sub-queries using CTEs (WITH)
>
> WITH group_items AS (  SELECT day, month, year, sum(sold_qty) AS daily_sale
> FROM items ... )
> , group_aggs AS ( SELECT day, month, year, daily_sale, sum(daily_sale) OVER
> (PARTITION BY month) FROM group_item )
>
> or instead of WINDOW functions you can write additional GROUP BY CTE queries
> for the different time-frames
>
> ..., month_total AS ( SELECT month, year, sum(daily_sale) FROM group_items
> GROUP BY month, year )
>
> and then combine these different CTE queries as you deem appropriate.
>
> http://www.postgresql.org/docs/9.3/interactive/sql-select.html   (the
> section for "WITH [RECURSIVE])
>
> David J.
>
>
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/sum-of-until-running-balance-and-sum-of-over-date-range-in-the-same-query-tp5776209p5776213.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
Thank you.  This will take a while to digest.  I have used window functions
    billable_days;
-- if a subscription is ceased same day it's started,-- that day is still chargable, so bump itIF billable_days < 1

(for running balance), and knew this would require window functions, but 
seems like I did not know how to use them properly.  I did not know you 
could mix them the way you did here.

Greatly appreciate it.

Mark