Percent to total in PostgreSQL without subquery

user_717095 0
Viewed 18921

I have a table with users. Each user has a country. What I want is to get the list of all countries with the numbers of users and the percent/total. What I have so far is:

SELECT
country_id,
COUNT(*) AS total,
((COUNT(*) * 100) / (SELECT COUNT(*) FROM users WHERE cond1 = true AND cond2 = true AND cond3 = true)::decimal) AS percent
FROM users
WHERE cond1 = true AND cond2 = true AND cond3 = true
GROUP BY contry_id

Conditions in both of queries are the same. I tried to do this without a subquery but then I can't get the total number of users but total per country. Is there a way to do this without a subquery? I'm using PostgreSQL. Any help is highly appreciated. Thanks in advance

postgresql
4 Answers

This is really old, but both of the select examples above either don't work, or are overly complex.

SELECT
    country_id,
    COUNT(*),
    (COUNT(*) / (SUM(COUNT(*)) OVER() )) * 100
FROM
    users
WHERE
    cond1 = true AND cond2 = true AND cond3 = true
GROUP BY 
    country_id

The second count is not necessary, it's just for debugging to ensure you're getting the right results. The trick is the SUM on top of the COUNT over the recordset.

Hope this helps someone.

Also, if anyone wants to do this in Django, just hack up an aggregate:

class PercentageOverRecordCount(Aggregate):
    function = 'OVER'
    template = '(COUNT(*) / (SUM(COUNT(*)) OVER() )) * 100'

    def __init__(self, expression, **extra):
        super().__init__(
            expression,
            output_field=DecimalField(),
            **extra
        )

Now it can be used in annotate.

user_13890570

Using last PostgreSQL version the query can be next:

CREATE TABLE users (
    id serial,
    country_id int
);

INSERT INTO users (country_id) VALUES (1),(1),(1),(2),(2),(3);

select distinct
    country_id,
    round(
        ((COUNT(*) OVER (partition by country_id )) * 100)::numeric 
        / COUNT(*) OVER () 
    , 2) as percent
from users 
order by country_id
;

Result on SQLize.online

+============+=========+
| country_id | percent |
+============+=========+
| 1          | 50.00   |
+------------+---------+
| 2          | 33.33   |
+------------+---------+
| 3          | 16.67   |
+------------+---------+
user_33993560
Related