Solved

Postgres 9.2 Percent of totals

Posted on 2013-01-14
5
470 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 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

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.

Question has a verified solution.

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

Suggested Solutions

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…
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

772 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