Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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.
0
freshgrill
Asked:
freshgrill
  • 2
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now