PostgreSQL – How to Get Percentage of Group By

group bypostgresql

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 well the percentage calculation. The result always show me 25% for each of number of ticket per user.

Thanks

Best Answer

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;
Related Question