CODEWITHSUNDEEP
mysql
Tom Cruise
Tom Cruise

Reputation: 69

Do calculations in sql query

I'm stuck trying to do calculations in sql query. I have table attendance which looks like this:

roll   | class_id | status 
abc    |     1    |   0
qwe    |     1    |   0
zxc    |     2    |   1
xcv    |     1    |   1
mnb    |     2    |   1
poi    |     1    |   1
lkj    |     2    |   0

I have to apply formula here:

att= (count where roll="abc" status = "1" /  count where roll="abc" status = "1" + count where roll="abc" status = "0") * 100

And then show all rolls which have att more than 75 %

roll |   att
abc  |   80
xyz  |   100

I was doing this calculation after getting values in php. But now I need to get this done in query. I do it individually by 

select * from attendance where status="0" and roll="abc"

and then doing it again for status="1"

Can someone explain me ? How can I approach with the query ?

Upvotes: 0

Views: 66

Answers (1)

Barmar
Barmar

Reputation: 780818

You can use SUM() to get a total of the rows that match a condition, because the condition evaluates to 1 when it's true.

SELECT roll, ROUND(SUM(status = 1)/COUNT(*) * 100) AS att
FROM attendance
GROUP BY roll
HAVING att > 75

Upvotes: 3

Related Questions