CODEWITHSUNDEEP
mysqlsql
Sathish Robert
Sathish Robert

Reputation: 187

Calculation of percentage of group count(*)

Select * from Namelist;
Name      Age
Sathish   25
Sathish   65
Sathish   55
Sathish   45
Sathish   35
Jana      55
Jana      25
Jana      10
Bala      55
Bala      26

How to get Percentage value for given format;

Name   Count   Percentege
Sathish  5     50%
Jana     3     30%
Bala     2     20%

Kindly share sql query?

Upvotes: 5

Views: 7617

Answers (4)

John Woo
John Woo

Reputation: 263733

replace column name and try this:

SELECT  iName, 
    COUNT(iName) AS `Count`, 
    concat(FORMAT(((COUNT(iName) * 100) / NewPeople.iCount),2),'%') AS `Percentage`
FROM   people, (SELECT COUNT(iName) AS iCount FROM people) NewPeople 
GROUP BY iName;

Output:

Name   Count   Percentage
Sathish  5     50.00%
Jana     3     30.00%
Bala     2     20.00%

Upvotes: 1

Bohemian
Bohemian

Reputation: 425063

This is a slightly sexier version of some of the other answers - note the use of sum(100) to avoid the longer (and more mundane) count(*) * 100 :)

select name, count(*) as count, sum(100) / total as percentage
from namelist
cross join (select count(*) as total from namelist) x
group by 1

Upvotes: 10

Nicola Cossu
Nicola Cossu

Reputation: 56357

select
name,
count(name) as `count`,
count(name)/(select count(*) from namelist)*100 as pct
from namelist
group by name

Upvotes: 1

aleroot
aleroot

Reputation: 72636

This query(not tested) should work :

SELECT Name,
COUNT(*) AS Count,
(COUNT(*) / _total ) * 100 AS Percentege
FROM Namelist,
(SELECT COUNT(*) AS _total
  FROM Namelist) AS myTotal
GROUP BY Name;

Upvotes: 2

Related Questions