How to Use a SQL Window Function to Calculate a Percentage of an Aggregate

How to use a SQL window function to calculate a percentage of an aggregate

I think you are looking for this:

SELECT d1, d2, sum(v)/sum(sum(v)) OVER (PARTITION BY d1) AS share
FROM   test
GROUP  BY d1, d2;

Produces the requested result.

Window functions are applied after aggregate functions. The outer sum() in sum(sum(v)) OVER ... is a window function (attached OVER ... clause) while the inner sum() is an aggregate function.

Effectively the same as:

WITH x AS (
   SELECT d1, d2, sum(v) AS sv
   FROM   test
   GROUP  BY d1, d2
   )
SELECT d1, d2, sv/sum(sv) OVER (PARTITION BY d1) AS share
FROM   x;

Or (without CTE):

SELECT d1, d2, sv/sum(sv) OVER (PARTITION BY d1) AS share
FROM  (
   SELECT d1, d2, sum(v) AS sv
   FROM   test
   GROUP  BY d1, d2
   ) x;

Or @Mu's variant.

Aside: Greenplum introduced correlated subqueries with version 4.2. See release notes.

How to calculate percentage with a SQL statement

I have tested the following and this does work. The answer by gordyii was close but had the multiplication of 100 in the wrong place and had some missing parenthesis.

Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By Grade

Percentage from Total SUM after GROUP BY SQL Server

You don't need a cross join. Just use window functions:

SELECT P.PersonID, SUM(PA.Total),
       SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage
FROM Person P JOIN
     Package PA
     ON P.PersonID = PA.PackageFK
GROUP BY P.PersonID;

Note that you do not need the JOIN for this query:

SELECT PA.PersonID, SUM(PA.Total),
       SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage
FROM Package PA
GROUP BY PA.PersonID;

SQL Server does integer division. I do such calculations using decimal numbers so they make more sense.

Here is a SQL Fiddle, with two changes:

1. The database is changed to SQL Server.
2. The total is stored as a number rather than a string.

SQL - Calculate percentage on count(column)

If your DB engine does not support Window functions then you can use a subquery:

SELECT event, 
       count(event) as event_count,
       count(event) * 100.0 / (select count(*) from event_information) as event_percent
FROM event_information
group by event

calculate percent to total by combining 2 SQL aggregate functions

SQL Server does integer division. You can explicit convert the values to a numeric with decimal points. I often just multiply by 1.0:

select mktcoupons, count(*) as countof, 
    count(*)/(select 1.0*count(*) from X) as percentage
from X
group by mktcoupons;

That said, you don't need a subquery for this query. You can use window functions instead:

select mktcoupons, count(*) as countof, 
       count(*)/sum(1.0 * count(*)) over () as percentage
from X
group by mktcoupons;

This is particularly helpful when you have a complicated WHERE clause or JOINs that you would have to duplicate in the subquery.

Calculate percentage aggregating counts in SQL

You can try the following by creating a cte. Here is the demo.

with total_users as
(
    select
      BrowserName,
      count(distinct UserId) as total_users
    from myTable
    group by
      BrowserName
)    
select 
    BrowserName,
    round((total_users * 100.0)/sum(total_users) over (), 1) as pct,
    total_users as ct
from total_users

Output:

| browsername | pct   | ct  |
| ----------- | ----- | --- |
| Chrome      | 33.3  | 1   |
| Firefox     | 66.6  | 2   |

---

Related Topics