Tom Cruise Tom Cruise - 3 months ago 10
MySQL Question

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 ?

Answer

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
Comments