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,qtr4_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.
freshgrillAsked:
Who is Participating?
 
awking00Commented:
select customer, item,
q1v,round(q1v/sum(q1v) over (partition by customer)*100,0) q1custpct,
q2v,round(q2v/sum(q2v) over (partition by customer)*100,0) q2custpct,
q3v,round(q3v/sum(q3v) over (partition by customer)*100,0) q3custpct,
q4v,round(q4v/sum(q4v) over (partition by customer)*100,0) q4custpct
from summary;

select customer, item,
q1v,case when q1v=0 then 0 else round(q1v/sum(q1v) over (partition by item)*100,0) end q1itempct,
q2v,case when q2v=0 then 0 else round(q2v/sum(q2v) over (partition by item)*100,0) end q2itempct,
q3v,case when q3v=0 then 0 else round(q3v/sum(q3v) over (partition by item)*100,0) end q3itempct,
q4v,case when q4v=0 then 0 else round(q4v/sum(q4v) over (partition by item)*100,0) end q4itempct
from summary;
0
 
earth man2Commented:
Look at window queries in the postgresql documents.
0
 
awking00Commented:
Can you provide some sample data and the expected results?
0
 
freshgrillAuthor Commented:
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);

Open in new window


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%
0
 
freshgrillAuthor Commented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.