CODEWITHSUNDEEP
sql
vipin tp
vipin tp

Reputation: 39

How to calculate percentage in sql?

I have to calculate percentage based on count. Given below is a sample data table.

TeamName   Count1   Count0
--------   -----    ------
Team1        1        2
Team2        3        0
Team3        1        1

I want to display the percentage based on the greatest value in Count1. The expecting output sample is given below:

Expecting Out Put :

TeamName   Count1   Count0  Percentage1  Percentage0
--------   -----    ------  -----------  -----------
Team1        1        2        33.33%         66.6%
Team2        3        0        100%           0%
Team3        1        1        33.33%         33.33%

Help me to find a proper solution. Thank You.

Upvotes: 1

Views: 671

Answers (5)

Shantanu
Shantanu

Reputation: 186

looking at your question, it seems if value is 1 then it is 33.33%, for 2 it is 66.6% & for 3 it is 100%. in this case yuo can try below:

SELECT COUNT1, COUNT0,
DECODE(COUNT1,1,'33.33%',2,'66.6%',3,'100%') PERCENTAGE1,
DECODE(COUNT0,1,'33.33%',2,'66.6%',3,'100%') PERCENTAGE0
FROM tablename;

if it is not the case, then please explain why for row3 in your table, both percentage1 and 0 are 33.33 %

Upvotes: -1

Pரதீப்
Pரதீப்

Reputation: 93694

use Max()over () trick to find the max of all the row.

select TeamName,   
Count1,  
Count0,
(count1*100.0)/nullif(Max(Count1) over(),0) Percentage1,
(count0*100.0)/nullif(max(Count1) over(),0) Percentage2
from yourtable

or Use a subquery to find the Max and do the math

SELECT 
  TeamName, 
  Count1, 
  Count0, 
  (Count1*100.0) / nullif((SELECT max(Count1) FROM yourTable),0) Percentage1, 
  (Count0*100.0) / nullif((SELECT max(Count1) FROM yourTable),0) Percentage2
FROM yourTable

Upvotes: 4

Ajay2707
Ajay2707

Reputation: 5798

try this.

create table tbl(teamname nvarchar(100), count1  int, count2  int)

insert into tbl values
('T1',1,2), ('T2',3,0), ('T3',1,1)

select 
    teamname,
    count1,    
    count2,
    count1 * 100 /(count1 + count2) Percenta1,
    count2 * 100 /(count1 + count2) Percenta2   

From tbl

drop table tbl

Upvotes: 1

DirkNM
DirkNM

Reputation: 2664

Maybe this will help you:

SELECT a.TeamName,
       a.Count1,
       a.Count0,
       a.Count1 / b.maxCount * 100
       a.Count0 / b.maxCount * 100
  FROM yourTable a
  JOIN(SELECT MAX(Count1) 
         FROM yourTable
      ) b
    ON 1 = 1;

Upvotes: 1

Taras Velykyy
Taras Velykyy

Reputation: 1801

SELECT 
  TeamName, 
  Count1, 
  Count0, 
  Count1 / (SELECT MAX(Count1) FROM Tbl), 
  Count0 / (SELECT MAX(Count1) FROM Tbl) 
FROM Tbl

What out for zero values in Count1. I can improve answer, if you describe case what to do when MAX(Count1) is zero.

Upvotes: 1

Related Questions