Do calculations in sql query

Question

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 ?

Barmar · Accepted Answer · 2016-08-20 22:16:05Z

3

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

edited Aug 20, 2016 at 22:16

answered Aug 20, 2016 at 22:05

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Cruise Over a year ago

I think you didn't understand my question.

Tom Cruise Over a year ago

I have to count the rows which have status=0 and status=1 instead of sum

Barmar Over a year ago

On the rows with 0, status = 0 returns 1. When you sum them, that counts those rows.

Barmar Over a year ago

Maybe it would be more obvious if I wrote SUM(IF(status = 0, 1, 0)). But it's the same thing.

Barmar Over a year ago

I've updated the answer so it divides by the count of both statuses.

Collectives™ on Stack Overflow

Do calculations in sql query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related