Percent to Total in Postgresql Without Subquery

Percent to total in PostgreSQL without subquery

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

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

Best explanation i could use to describe it is: basically it allows you to do a group by on one field without the group by clause.

I believe this might 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

% of total calculation without subquery in Postgres

Use window functions:

SELECT ID, COUNT(*),
       COUNT(*) / SUM(COUNT(*)) OVER () AS "% OF TOTAL"
FROM DATA
GROUP BY ID;

Can I add a total to the result without a subquery?

Use grouping sets:

SELECT YEAR(TimeStamp) Year, 1 + MONTH(TimeStamp) / 7 Half, count(*) ct
FROM Table1
WHERE Row1 = 'Blah'
GROUP BY GROUPING SETS (YEAR(TimeStamp), 1 + MONTH(TimeStamp) / 7, ())
ORDER BY Year DESC, Half ASC;

Postgresql Trying to calculate percentage of total using over(). Never used over() before but I've read this is the proper approach

You can do this with conditional aggregation:

select
    sum(good_job) filter(where age between 18 and 24) 18_24_GoodJobs,
    sum(good_job) filter(where age between 18 and 24)
        / sum(good_job) 18_24_GoodJobs_Part
from jf_q2
where working = 1

This gives you the count of good jobs for age 18-24 and the proportion of age 18-24 amongst good jobs (as a decimal value).

How could I calculate the percentage of total in SQL?

Try using window functions:

count(modelo_venta) * 100.0 / sum(count(modelo_venta)) over () as porcentaje,

The issue is that you want to count based on the results of the query, not the entire table.

Get percentages of the total for each group

The problem is with the subquery on the left side of the division, which returns multiple rows. In your aggregate query, you can just use count(*) to get the number of rows in the users table that have the same campaign_id. As for the denumerator of the division (the total number of rows), you don't need a subquery either - just use window functions.

So:

select
    campaign_id,
    1.0 * count(*) / sum(count(*)) over() percentage
from users
group by campaign_id

---

Related Topics