Re: sum of until (running balance) and sum of over date range in the same query - Mailing list pgsql-sql

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.