agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: sum of until (running balance) and sum of over date range in the same query
Date: Mon, 28 Oct 2013 21:17:18 -0700 (PDT)
Message-ID: <1383020238958-5776213.post@n5.nabble.com> (raw)
In-Reply-To: <526F2EC0.2030300@turnkey.bz>
References: <526F2EC0.2030300@turnkey.bz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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-t...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (3+ messages) latest in thread
Message-ID: <1383020238958-5776213.post@n5.nabble.com>
Permalink: ../1383020238958-5776213.post@n5.nabble.com/
Also on: postgresql.org/message-id/1383020238958-5776213.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: sum of until (running balance) and sum of over date range in the same query
In-Reply-To: <1383020238958-5776213.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox