Get percentage of group by

Question

I have this data:

CREATE TABLE tickets(user_id int NOT NULL);
INSERT INTO tickets VALUES (1);
INSERT INTO tickets VALUES (2);
INSERT INTO tickets VALUES (3); -- 3 times
INSERT INTO tickets VALUES (4); -- 10 times

Now I want to display the percentage of the number of tickets per user.

I tried this:

WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets 
  GROUP BY user_id
)
SELECT 
  number_of_tickets_per_user, 
  ROUND((COUNT(user_id) * 100.0) / (SELECT COUNT(DISTINCT(user_id)) FROM tickets), 3) -- No no no no
FROM number_of_tickets
GROUP BY number_of_tickets_per_user
ORDER BY number_of_tickets_per_user;

But I probably don't handle the percentage calculation well. The result always shows me 25% for each of the number of tickets per user.

Answer 1

WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets 
  GROUP BY user_id
),
total_tickets AS (
  SELECT SUM(number_of_tickets_per_user) total_tickets
  FROM number_of_tickets
)
SELECT 
  user_id,
  number_of_tickets_per_user, 
  ROUND(number_of_tickets_per_user / total_tickets,3) percent
FROM number_of_tickets, total_tickets;

Answer 2

I would suggest to use window functions:

 SELECT DISTINCT user_id,
        (count(*) over (partition by user_id) / count(*) over ()::numeric)
   FROM tickets
   ORDER BY user_id;

 user_id |        ?column?
---------+------------------------
       1 | 0.06666666666666666667
       2 | 0.06666666666666666667
       3 | 0.20000000000000000000
       4 | 0.66666666666666666667
(4 rows)

Answer 3

    begin;
    CREATE TABLE tickets(user_id int NOT NULL);
    INSERT INTO tickets VALUES (1);
    INSERT INTO tickets VALUES (2);
    INSERT INTO tickets select 3 from generate_series(1,3) s;
    INSERT INTO tickets select 4 from generate_series(1,10) s;
    end;
    
    WITH cte AS (
        SELECT
            user_id,
            count(user_id) AS count
        FROM
            tickets
        GROUP BY
            ROLLUP (user_id)
        ORDER BY
            1
    ),
    cte1 AS (
    SELECT
        user_id,
        count,
        count * 1.0 / first_value(count) OVER (ORDER BY user_id nulls FIRST) AS percent,
            first_value(count) OVER (ORDER BY user_id nulls FIRST) AS total_tickets
            FROM
                cte
            ORDER BY
                user_id nulls FIRST
    )
            SELECT
                user_id,
                count AS number_of_tickets_per_user,
                total_tickets,
                percent
            FROM
                cte1
            WHERE
                user_id IS NOT NULL
            ORDER BY
                1;

another way, use rollup. second cte is needed.