CODEWITHSUNDEEP
sqlpostgresql
RTL
RTL

Reputation: 3

How do I calculate percentages within group by in postgresql?

I have a query that groups a company's name, size and revenue. This is my query:

with Test as
(select
id.name as Company
,CASE WHEN li.segment = 'Large' then 'Large Cap'
      WHEN li.segment = 'Medium' then 'Mid Cap'
      WHEN li.segment = 'Small' then 'Small Cap' else NULL end as Size
,sum(ia.amount) as Revenue
from base.company_rev ia
join base.company_detail id on id.company_account_id = ia.company_account_id
left join base.product_issued li on li.product_id = ia.product_id
where 1 = 1
and ia.create_date::date between '2018-05-01' and '2018-05-31'
group by id.name, li.segment
order by 1, 2, 3)

 select * 
 from Test
 group by company, size, revenue
 order by 1, 2, 3

How do I add a percentage of revenue that each company's size is attributed to? I want to do it based on the dollar amount within the size groupings.

Ex.

Company A...Large Cap...15m = 60% (15/25)
Company A...Mid Cap...10m = 40% (10/25)

Upvotes: 0

Views: 1649

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You are looking for a window function, namely SUM() OVER().

with test as
(
  select
    id.name as company
    ,case when li.segment = 'Large' then 'Large Cap'
          when li.segment = 'Medium' then 'Mid Cap'
          when li.segment = 'Small' then 'Small Cap' 
          else null end as size
    ,sum(ia.amount) as revenue
  from base.company_rev ia
  join base.company_detail id on id.company_account_id = ia.company_account_id
  left join base.product_issued li on li.product_id = ia.product_id
  where 1 = 1
  and ia.create_date::date between date '2018-05-01' and date '2018-05-31'
  group by id.name, li.segment
)
select
  company, 
  size, 
  revenue,
  revenue / sum(revenue) over (partition by company) * 100 as percentage
from test
order by company, size, revenue;

Upvotes: 2

iamdkrz
iamdkrz

Reputation: 21

(ia.amount/sum(ia.amount))*100 as Percentage

Upvotes: 0

Related Questions