13

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.

1
  • 2
    Unrelated, but: distinct is not a function. distinct a is exactly the same thing as distinct (a)
    – user1822
    Commented Aug 29, 2018 at 12:35

3 Answers 3

7
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;
1
  • Thanks that's exactly what I want and I love this answer with CTE.
    – Mio
    Commented Aug 29, 2018 at 12:47
9

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)
0
    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.