Percentage of the total in PostgreSQL without a subquery

Question

I have a table with users. Each user has a country. I want to get a list of all countries with the number of users and the percentage / total. What I still have:

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 

The conditions in both queries are the same. I tried to do this without a subquery, but then I can’t get the total number of users, but all over the country. Is there any way to do this without a subquery? I am using PostgreSQL. Any help is much appreciated. thanks in advance

Answer 1

I am not a PostgreSQL user, but a common solution would be to use window functions.

Read how to use this with http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html

The best explanation I could use to describe this: basically it allows you to make a group in one field without a group by clause.

I believe this can do the trick:

 SELECT country_id, COUNT(*) OVER (country_id) ((COUNT(*) OVER (country_id)) * 100) / COUNT(*) OVER () )::decimal) as percent FROM users WHERE cond1 = true AND cond2 = true AND cond3 = true 

Answer 2

I assume that the reason you want to exclude the subquery is to not double check the user table. Remember that the sum is the sum of the calculations for each country.

 WITH c AS (SELECT country_id, count(*) AS cnt FROM users WHERE cond1=... GROUP BY country_id) SELECT *, 100.0*cnt/(SELECT sum(cnt) FROM c) AS percent FROM c; 

This request creates a small CTE with statistics for each country. It will only scan the user table once and create a small result set (only one row for each country).

The sum (SELECT sum (cnt) FROM c) is calculated only once on this small result set, so it uses little time.

You can also use the window function:

 SELECT country_id, cnt, 100.0*cnt/(sum(cnt) OVER ()) AS percent FROM (SELECT country_id, count(*) as cnt from users group by country_id) foo; 

(this is the same as a nightwolf request with lol errors removed)

Both requests take the same time.