CODEWITHSUNDEEP
sqloracle-apexoracle-apex-5.1
user1544327
user1544327

Reputation: 117

Is there a way to get percentage for earch field

I need percentage for each status

Select NAME, STATUS, Count(*) From DATA 

group by NAME, STATUS
order by NAME

Result is here

I need percentage for each of the statues

Upvotes: 0

Views: 75

Answers (4)

Binara Medawatta
Binara Medawatta

Reputation: 532

Check this, using SUM to calculate the total and then use group by to group the names

SELECT name, status, COUNT(status)/(
    SELECT COUNT(status) 
    FROM data
)*100
FROM percentage
GROUP BY name, status
ORDER BY name

Upvotes: 0

Kiran Patil
Kiran Patil

Reputation: 339

I think you have used below formula in your percentage calculate, count(row first count)*100/sum of all count rows, i.e.448*100/1560=28.71. So, dear, I request you please mention all the problems with your question.

Try the below query and enjoy it.

 Select NAME, STATUS, Count(*),
     Count(*)*100/(select sum(CT) from (select NAME, STATUS, Count(*) CT from data group by NAME, STATUS))PERCENTAGE
     From DATA 
       group by NAME, STATUS
        order by NAME

Upvotes: 1

Dhaval Soni
Dhaval Soni

Reputation: 416

You need to create subquery for count without status, then you need to get percentage user wise, you can try this below:

Select f1.NAME, f1.STATUS, Count(*),CONCAT((Count(*)*100)/(select Count(*) from data f2 where NAME=f1.Name group by f2.NAME),'%') From DATA f1

group by f1.NAME, f1.STATUS
order by f1.NAME

Or if you have user id then it's better query and perfect result

Select f1.NAME, f1.STATUS, Count(*),CONCAT((Count(*)*100)/(select Count(*) from data f2 where UserId=f1.UserId group by f2.UserId),'%') From DATA f1

group by f1.UserId,f1.NAME, f1.STATUS
order by f1.NAME

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13016

Here's how to get the percentage. First is to get the total count(), then multiple by 1.0, to cast the result in decimal format. 

select name, status, count(1) 
    ,  ((count(1) * 1.0)
        /(select count(1) from data)) * 100 as percentage
from data
group by name, status
order by name

Upvotes: 0

Related Questions