CODEWITHSUNDEEP
sqldatabaseoracle-database
Ryan Lenz
Ryan Lenz

Reputation: 7

How to find percentage of value in table?

I have a SQL table that contains some of the following data: 

ID  Name                Grade
54  Brooke  Hammond     Pass
54  Brooke  Hammond     Pass
54  Brooke  Hammond     Fail
82  Carlos  Vaughn      Fail
88  Christina  Rhodes   Fail
88  Christina  Rhodes   Fail
73  Claude  Brady       Fail
86  Clifford  Larson    Fail
78  Darrel  Roy         Pass
75  Darren  Ramos       Pass
59  Delores  Fisher     Fail
50  Elaine  Parsons     Fail
99  Frankie  Figueroa   Fail
96  Geoffrey  Parsons   Fail
96  Geoffrey  Parsons   Fail
96  Geoffrey  Parsons   Pass
84  Gordon  Parsons     Pass
84  Gordon  Parsons     Fail

I'm looking for a SQL statement that will find the percentage of fail scores associated with each person.

The final output I'm looking for is something like Name1: 30%, Name2: 40%, Name3: 20%, etc...

Upvotes: 0

Views: 66

Answers (2)

Popeye
Popeye

Reputation: 35930

In oracle, you can leverage avg aggregate function as following:

Select id, name,
       Avg(case when grade = 'Fail' then 1 else 0 end) * 100 as pecntg_failure
from your_table
Group by id, name;

Cheers!!

Upvotes: 4

developerjack
developerjack

Reputation: 1223

You can use an IF function to turn pass fail into 1's and 0's and simply sum the result. An example query could look like:

select 
name, 
SUM(IF(grade="Pass", 1, 0)) as pass, 
count(grade) as count_Grades,
SUM(IF(grade="Pass", 1, 0)) / count(grade) as percentage 
from marks group by marks.name;

For the full example I threw together this db fiddle based on your sample data.

CREATE TABLE marks (
  `id` INTEGER,
  `name` VARCHAR(9),
  `surname` VARCHAR(15),
  `grade` VARCHAR(8)
);


INSERT INTO marks
  (`id`, `name`, `surname`, `grade`)
VALUES
  ('54', 'Brooke', 'Hammond', 'Pass'),
  ('54', 'Brooke', 'Hammond', 'Pass'),
  ('54', 'Brooke', 'Hammond', 'Fail'),
  ('82', 'Carlos', 'Vaughn', 'Fail'),
  ('88', 'Christina', 'Rhodes', 'Fail'),
  ('88', 'Christina', 'Rhodes', 'Fail'),
  ('73', 'Claude', 'Brady', 'Fail'),
  ('86', 'Clifford', 'Larson', 'Fail'),
  ('78', 'Darrel', 'Roy', 'Pass'),
  ('75', 'Darren', 'Ramos', 'Pass'),
  ('59', 'Delores', 'Fisher', 'Fail'),
  ('50', 'Elaine', 'Parsons', 'Fail'),
  ('99', 'Frankie', 'Figueroa', 'Fail'),
  ('96', 'Geoffrey', 'Parsons', 'Fail'),
  ('96', 'Geoffrey', 'Parsons', 'Fail'),
  ('96', 'Geoffrey', 'Parsons', 'Pass'),
  ('84', 'Gordon', 'Parsons', 'Pass'),
  ('84', 'Gordon', 'Parsons', 'Fail');

Upvotes: -1

Related Questions