freshgrill
asked on
Postgres 9.2 Percent of totals
I have a summary table with total purchase by customer, item ,quarter:
customer, item, qtr1_total,qtr2_total,qtr3 _total,qtr 4_total
Using Postgres 9.2, how would I get:
1) Percent of total of customer by item by qtr.
i.e. Shirts is 32% of customer ABC purchases for qtr1, 28% of customer ABC purchases qtr 2, etc...
2) Percent of total of item by customer by qtr.
i.e. Customer ABC shirt purchases was 2% of total shirt purchases for qtr1, 4% of total shirt purchases for qtr2 ,etc..
Ideally, would like to get both in same query but if not feasible, then 2 different queries can be used.
customer, item, qtr1_total,qtr2_total,qtr3
Using Postgres 9.2, how would I get:
1) Percent of total of customer by item by qtr.
i.e. Shirts is 32% of customer ABC purchases for qtr1, 28% of customer ABC purchases qtr 2, etc...
2) Percent of total of item by customer by qtr.
i.e. Customer ABC shirt purchases was 2% of total shirt purchases for qtr1, 4% of total shirt purchases for qtr2 ,etc..
Ideally, would like to get both in same query but if not feasible, then 2 different queries can be used.
Look at window queries in the postgresql documents.
Can you provide some sample data and the expected results?
ASKER
create table summary (customer int, item int, q1v int, q2v int, q3v int, q4v int);
insert into summary values
(1,100,50,75,30,20),
(1,200,75,0,0,40),
(1,300,25,75,30,0),
(2,100,10,75,30,20),
(2,200,75,0,0,40),
(3,100,10,75,30,20),
(3,300,25,75,30,0);
Output:
Customer Item q1v %of cust q2v %of cust q3v %of cust q4v %of cust
1 100 50 33% 75 50% 30 50% 20 33%
1 200 75 50% 0 0% 0 0% 40 67%
1 300 25 17% 75 50% 30 50% 0 0%
2 100 10 12% 75 100% 30 100% 20 33%
2 200 75 88% 0 0% 0 0% 40 67%
3 100 10 29% 75 50% 30 50% 20 100%
3 300 25 71% 75 50% 30 50% 0 0%
Customer Item q1v %of item q2v %of item q3v %of item q4v %of item
1 100 50 71% 75 33% 30 33% 20 33%
2 100 10 14% 75 33% 30 33% 20 33%
3 100 10 14% 75 33% 30 33% 20 33%
1 200 75 50% 0 0% 0 0% 40 50%
2 200 75 50% 0 0% 0 0% 40 50%
1 300 25 50% 75 50% 30 50% 0 0%
3 300 25 50% 75 50% 30 50% 0 0%
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In order to get the percentages right, I had to add ::numeric, but it works great.
select customer, item,
q1v,round(q1v::numeric/sum (q1v) over (partition by customer)*100,0) q1custpct,
q2v,round(q2v::numeric/sum (q2v) over (partition by customer)*100,0) q2custpct,
q3v,round(q3v::numeric/sum (q3v) over (partition by customer)*100,0) q3custpct,
q4v,round(q4v::numeric/sum (q4v) over (partition by customer)*100,0) q4custpct
from summary;
select customer, item,
q1v,case when q1v=0 then 0 else round(q1v::numeric/sum(q1v ) over (partition by item)*100,0) end q1itempct,
q2v,case when q2v=0 then 0 else round(q2v::numeric/sum(q2v ) over (partition by item)*100,0) end q2itempct,
q3v,case when q3v=0 then 0 else round(q3v::numeric/sum(q3v ) over (partition by item)*100,0) end q3itempct,
q4v,case when q4v=0 then 0 else round(q4v::numeric/sum(q4v ) over (partition by item)*100,0) end q4itempct
from summary;
select customer, item,
q1v,round(q1v::numeric/sum
q2v,round(q2v::numeric/sum
q3v,round(q3v::numeric/sum
q4v,round(q4v::numeric/sum
from summary;
select customer, item,
q1v,case when q1v=0 then 0 else round(q1v::numeric/sum(q1v
q2v,case when q2v=0 then 0 else round(q2v::numeric/sum(q2v
q3v,case when q3v=0 then 0 else round(q3v::numeric/sum(q3v
q4v,case when q4v=0 then 0 else round(q4v::numeric/sum(q4v
from summary;