Get percentages of the total for each group

Question

Using Postgresql 12.2

The table 'users' has a list of userids and campaign_ids to show what ad campaign brought the users in. I am trying to show the percentage of the total that each campaign_id brought in.

The desired outcome is something like:

campaign_id      | percentage
    -------------+-------------------
     AC          | 0.18
     SM          | 0.37
     BC          | 0.24
     TI          | 0.21

When I try

select campaign_id, (select 
               count(userid) over (partition by campaign_id)
                   from users
                       )
                      /
                     (select 
                         count(userid)
                         from users) as percentage
  from users
  group by campaign_id
;

I get the error "ERROR: more than one row returned by a subquery used as an expression."

Answer 1

The problem is with the subquery on the left side of the division, which returns multiple rows. In your aggregate query, you can just use count(*) to get the number of rows in the users table that have the same campaign_id. As for the denumerator of the division (the total number of rows), you don't need a subquery either - just use window functions.

So:

select
    campaign_id,
    1.0 * count(*) / sum(count(*)) over() percentage
from users
group by campaign_id