Solved

Postgres 9.2 Percent of totals

Posted on 2013-01-14
5
468 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
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

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

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 31

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now