Solved

Postgres 9.2 Percent of totals

Posted on 2013-01-14
5
473 Views
Last Modified: 2013-01-16
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
Comment
Question by:freshgrill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 38778592
Look at window queries in the postgresql documents.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38778625
Can you provide some sample data and the expected results?
0
 

Author Comment

by:freshgrill
ID: 38780394
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
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 38782979
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
 

Author Closing Comment

by:freshgrill
ID: 38783696
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question