MySQL Icon

MySQL Sum If Example

Here is a really good tip for using MySQL SUM IF functions if you need to do a bunch of different counts or sums on the same data set based off some conditional statements.

Say you have a table of students grades and you want some stats based off the student’s grades. It would very easy to just select the entire data set and use PHP to process all the data and get the proper stats but take a look at the following query using IF, SUM and COUNT in MySQL.

MySQL SUM IF Query

SELECT COUNT(id) as total_students, SUM(IF(grade>=50,1,0)) as pass, SUM(IF(grade<50,1,0)) as fail FROM students_grades

We use the IF function in conjunction with the SUM function to get the number of students that pass and fail. For those of you unfamiliar with the IF statement, the first argument is your comparison, the second is your true statement and the third is your false statement. So for the pass variable above I’m adding one if the grade is greater than or equal to 50 and adding 0 if it’s less than or equal to 50.

I love finding ways to take more advantage of the MySQL engine and cut down on any PHP code I need to write. I hope this MySQL SUM IF example helps your code be more efficient.

Resources

MySQL IF Function
MySQL SUM Function

Continue Reading