agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedsum of until (running balance) and sum of over date range in the same query
3+ messages / 2 participants
[nested] [flat]
* sum of until (running balance) and sum of over date range in the same query
@ 2013-10-29 03:42 M. D. <lists@turnkey.bz>
2013-10-29 04:17 ` Re: sum of until (running balance) and sum of over date range in the same query David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: M. D. @ 2013-10-29 03:42 UTC (permalink / raw)
To: pgsql-sql
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) NOT NULL,
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sum of until (running balance) and sum of over date range in the same query
2013-10-29 03:42 sum of until (running balance) and sum of over date range in the same query M. D. <lists@turnkey.bz>
@ 2013-10-29 04:17 ` David Johnston <polobo@yahoo.com>
2013-10-29 13:19 ` Re: Re: sum of until (running balance) and sum of over date range in the same query M. D. <lists@turnkey.bz>
0 siblings, 1 reply; 3+ messages in thread
From: David Johnston @ 2013-10-29 04:17 UTC (permalink / raw)
To: 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-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Re: sum of until (running balance) and sum of over date range in the same query
2013-10-29 03:42 sum of until (running balance) and sum of over date range in the same query M. D. <lists@turnkey.bz>
2013-10-29 04:17 ` Re: sum of until (running balance) and sum of over date range in the same query David Johnston <polobo@yahoo.com>
@ 2013-10-29 13:19 ` M. D. <lists@turnkey.bz>
0 siblings, 0 replies; 3+ messages in thread
From: M. D. @ 2013-10-29 13:19 UTC (permalink / raw)
To: pgsql-sql
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-t...
> 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 it
IF 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-10-29 13:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-29 03:42 sum of until (running balance) and sum of over date range in the same query M. D. <lists@turnkey.bz>
2013-10-29 04:17 ` David Johnston <polobo@yahoo.com>
2013-10-29 13:19 ` M. D. <lists@turnkey.bz>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox