redshift or postgresql - create a new column to get users distribution

zealous 0
Viewed 30

I have aggregated column one column in the table as following

aggregated table:

*-------------------*
| territory | users |
*-------------------*
| worldwide |   20  |
| usa       |   6   |
| germany   |   3   |
| australia |   2   |
| india     |   5   |
| japan     |   4   | 
*-------------------*

Below is my desired result in which I want to replicate 'worldwide' data as new column so I can calculate distribution of users.

desired output:

*-----------------------------------------------------*
| territory | users | ww_user |  % users distribution |
*-----------------------------------------------------*
| worldwide |   20  |   20    |    0.0                |
| usa       |   6   |   20    |    0.30               |
| germany   |   3   |   20    |    0.15               | 
| australia |   2   |   20    |    0.10               | 
| india     |   5   |   20    |    0.25               | 
| japan     |   4   |   20    |    0.20               | 
*-----------------------------------------------------*

I have tried few options in db<>fiddle but none of them are getting me desired results.

can anyone give me a direction/idea on how to get my desired results?

amazon-redshift sql postgresql
2 Answers

You can add a CASE WHEN to replace the 1 in worldwide

SELECT territory,my.users, my2.users as "users worldwide", ROUND((my.users * 1.0 / my2.users)  ,2)  as "% users distribution"
  FROM my CROSS JOIN (SELECT users FROM my WHERE territory = 'worldwide') my2
territory users users worldwide % users distribution
worldwide 20 20 1.00
usa 6 20 0.30
germany 3 20 0.15
australia 2 20 0.10
india 5 20 0.25
japan 4 20 0.20 
SELECT 6

fiddle

nbk0

There are a number of ways to do this but it seems like fixing your fiddle would be easiest. https://dbfiddle.uk/YZLCxDxO

You had a typo (ww vs worldwide) and you need to use the SUM() window function. Otherwise you were on the right track.

with ww_case as (
select territory, users,
  sum(case when territory = 'worldwide' then users else 0 end) over () as ww_users
from my
group by
  1, 2
)
select territory, users, ww_users, users::decimal(5,3) / ww_users as perc
from ww_case
Bill Weiner0
Related