Question 114606 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search