Compute Percents from Sum() in the Same Select SQL Query

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.

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

Get column sum and use to calculate percent of total (mySQL)

You just need to CROSS JOIN the SUM() of Number column:

SELECT Name, Number, Number * 100 / t.s AS `% of total`
FROM mytable
CROSS JOIN (SELECT SUM(Number) AS s FROM mytable) t

Demo Here

Compute percents from SUM() in the same SELECT sql query

There is more to this question than it may seem.

Simple version

This is much faster and simpler:

SELECT property_name
      ,(count(value_a = value_b OR NULL) * 100) / count(*) AS pct
FROM   my_obj
GROUP  BY 1;

Result:

property_name | pct
--------------+----
 prop_1       | 17
 prop_2       | 43

How?

• You don't need a function for this at all.

• Instead of counting value_b (which you don't need to begin with) and calculating the total, use count(*) for the total. Faster, simpler.

• This assumes you don't have NULL values. I.e. both columns are defined NOT NULL. The information is missing in your question.
  
  If not, your original query is probably not doing what you think it does. If any of the values is NULL, your version does not count that row at all. You could even provoke a division-by-zero exception this way.
  
  This version works with NULL, too. count(*) produces the count of all rows, regardless of values.

• Here's how the count works:

   TRUE  OR NULL = TRUE
   FALSE OR NULL = NULL
  

  count() ignores NULL values. Voilá.

• Operator precedence governs that = binds before OR. You could add parentheses to make it clearer:

  count ((value_a = value_b) OR FALSE)
  

• You can do the same with

  count NULLIF(<expression>, FALSE)
  

• The result type of count() is bigint by default.
  
  A division bigint / bigint, truncates fractional digits.

Include fractional digits

Use 100.0 (with fractional digit) to force the calculation to be numeric and thereby preserve fractional digits.

You may want to use round() with this:

SELECT property_name
      ,round((count(value_a = value_b OR NULL) * 100.0) / count(*), 2) AS pct
FROM   my_obj
GROUP  BY 1;

Result:

property_name | pct
--------------+-------
 prop_1       | 17.23
 prop_2       | 43.09

As an aside:

I use value_a instead of valueA. Don't use unquoted mixed-case identifiers in PostgreSQL. I have seen too many desperate question coming from this folly. If you wonder what I am talking about, read the chapter Identifiers and Key Words in the manual.

Calculate percentage value for each row based on total column value in SQL Server

You can use the SUM() function as analytic function over the entire table to compute the total sum. Then, just divide each region's cost by that sum to obtain the percentage.

SELECT
    Region,
    Cost,
    100 * Cost / SUM(Cost) OVER () AS Percentage
FROM yourTable

Note that you could have also used a non correlated subquery to find the total cost, e.g.

(SELECT SUM(Cost) FROM yourTable)

But the first version I gave you might outperform if for no other reason than it requires running only a single query.

Output:

Demo here:

Rextester

Update:

For your updated query I might use the following:

WITH cte AS (
    SELECT
        Region,
        SUM(Cost) AS sum_cost
    FROM yourTable
    GROUP BY Region
)

SELECT
    Region,
    sum_cost,
    100 * sum_cost / SUM(sum_cost) OVER () AS Percentage
FROM cte;

Demo

SQL - Calculate percentage on count(column)

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

Calculating percentages in SQL query

Basically, you can take your query and then join it again with all the weeks. This gives you the first three columns. You can fill in the last two using window functions:

with data as (
      SELECT WeekNo, SUM(MyAmount) As TheAmount
      FROM TryIt
      GROUP BY WeekNo
     )
select w.weekno, d.weekno as pctweek, d.theamount,
       sum(d.theamount) over (partition by w.weekno) as cumulative,
       d.theamount * 100.0 / sum(d.theamount) over (partition by w.weekno)  as pct
from data d join
     (select distinct weekno from TryIt) w
     on d.weekno <= w.weekno
order by 1, 2;

Here is a db<>fiddle.

Use COUNT * To Calculate Percentage In Same SELECT Statement

Here is one alternative that should work using two sub queries.

SELECT
    total_ct,
    match_ct,
    pct_match=CASE WHEN (total_ct>0) THEN ... ELSE 0 END
FROM
(
    SELECT
        total_ct=COUNT(*),
        match_ct=SUM(MatchedFlag)
    FROM
    (
        SELECT
            someid,
            MatchedFlag=CASE WHEN is_matched=1 THEN 1 ELSE 0 END
        FROM
            candidate cd
    )AS Z   
)AS Y

How to calculate percentage for sum of difference of two columns in sql

Like this?

SELECT sum([BillCount]) - sum([IssueCount]) as [Difference],
    (sum(IssueCount) - sum(BillCount)) / nullif(sum(BillCount), 0) as ptcdiff
FROM Invoice
where [Year] = '2015'

You'll note I changed the order of subtraction. This makes it so that its a negative change rather than a positive change. If you need an absolute. change, you can just throw abs() around the result. You can also bake this into a function if it makes sense to

declare 
    @pctOld float = 260918,
    @pctNew float = 16684  

select PctDiff = case when @PctNew is null or @PctOld is null then null
                       when @PctNew = null or @PctOld = null then null
                       when @PctNew = 0 and @PctOld = 0 then 0    
                       when @PctNew = 0 and @PctOld > 0 then -100.00
                       when @PctNew >= 0 and @PctOld < 0 then null
                       when @PctNew < 0 and @PctOld > 0 then null
                       when @PctOld = 0 then null
                       else ((@PctNew - @PctOld) / abs(@PctOld)) * 100.0
                  end

---

Related Topics