Percentage from Total Sum After Group by SQL Server

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.

Calculating percentages with GROUP BY query

WITH t1 AS 
 (SELECT User, Rating, Count(*) AS n 
  FROM your_table
  GROUP BY User, Rating)
SELECT User, Rating, n, 
       (0.0+n)/(COUNT(*) OVER (PARTITION BY User)) -- no integer divide!
FROM t1;

Or

SELECT User, Rating, Count(*) OVER w_user_rating AS n, 
        (0.0+Count(*) OVER w_user_rating)/(Count(*) OVER (PARTITION BY User)) AS pct
FROM your_table
WINDOW w_user_rating AS (PARTITION BY User, Rating);

I would see if one of these or the other yields a better query plan with the appropriate tool for your RDBMS.

Calculating percentage within a group

You can do it with a sub-select and a join:

SELECT t1.sex, employed, count(*) AS `count`, count(*) / t2.total AS percent
  FROM my_table AS t1
  JOIN (
    SELECT sex, count(*) AS total 
      FROM my_table
      GROUP BY sex
  ) AS t2
  ON t1.sex = t2.sex
  GROUP BY t1.sex, employed;

I can't think of other approaches off the top of my head.

In SQL how to get percent of group by date?

If you want the proportion within a date, then date should be in the partition by:

SELECT "Date", "Region", SUM("Sales") as "TotalUSD",
       SUM("Sales") * 100.0 / SUM(SUM("Sales"))  OVER (PARTITION BY "Date") AS Percentage
FROM "sales_db"."sales_table"
GROUP BY "Date", "Region";

Note: I strongly advise you to get rid of the double quotes in the identifier names. If necessary, re-create the table. All the double quotes just make the query harder to write and to read.

Also, I added a .0. Some databases do integer division, which can also throw results off a bit.

Display Percentage of grouped SUM to total SUM

You can do this with window functions:

with t as (
      <your query here>
     )
select t.*,
       profit * 100.0 / sum(profit) over () as profit_percentage
from t;

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

SQL Finding a percentage of total of one column

Use window functions:

select employee, sum(requests) as total_requests,
       sum(requests) * 1.0 / sum(sum(requests)) over () as ratio
from t
group by employee;

The * 1.0 is simply to avoid integer division (assuming that requests is an integer).

How to get a percentage of total when the query has a GROUP BY?

For large sets, a JOIN may perform better than the subquery.

SELECT ma.actor
     , COUNT(1) AS total
     , COUNT(1) / t.cnt * 100 AS `percentage`
  FROM movies_actors ma
 CROSS
  JOIN (SELECT COUNT(1) AS cnt FROM movies_actors) t
 GROUP
    BY ma.actor
     , t.cnt  

For large sets, and when a large percentage of the rows are being returned, the JOIN operation can usually outperform a subquery. In your case, it's not a correlated subquery, so MySQL shouldn't have to execute that multiple times, so it may not make any difference.

Note to non-fans of COUNT(1)... we could replace any and all occurrences of COUNT(1) with COUNT(*) or IFNULL(SUM(1),0) to achieve equivalent result.

---

Related Topics