Count with If Condition in MySQL Query

Count() function in MySQL is to get the number of rows in a MySQL table or expression. In this article, we will talk about the count() function with if().

We are creating a table students_data followed by adding data to it.

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(255),
student_subject VARCHAR(255),
PRIMARY KEY (student_id)
);
INSERT INTO students_data (student_name,student_address,student_grade,student_subject) 
VALUES("Gustav","56A Denmark","A","Physics"),
("Henric","255 USA","B","Geography"),
("Richa","78 India","C","Physics"),
("Margit","58 Canada","A","Physics"),	
("Rasmus","18 Libya","B","Physics"),
("Erick","43 Sweden","C","Geography"),
("Tanya","78 Singapore","D","Geography"),
("Monika","255 Italy","A","Chemistry"),
("Atharv","587 California","A","Chemistry"),	
("Eva","18 Singapore","D","Physics"),
("Joan","43 Germany","D","Physics"),
("Jacob","78 Singapore","C","Chemistry"),
("Thomas","258 Germany","C","Geography"),
("Rohit","899 Bangladesh","A","Geography");

Let us see what got into table students_data by executing:

 SELECT * FROM students_data;

Output:-

Since the data got created now, let us forge ahead by looking into the syntax of COUNT() with IF().

Syntax:-

SELECT [DISTINCT] COUNT([DISTINCT] IF(<condition>, <expression>, NULL)) AS alias_name FROM your_table_name;

The syntax shows that:

• COUNT() function includes IF() function, which has a condition specified.
• If the <condition> is true, then the count will be calculated based on <expression> passed. Else, NULL is passed in the count() function. In case NULL is passed to count(), it will not get the count of the results, instead it will get the count of the null values in the column your_column_name.
• The DISTINCT keyword is optional.
• alias_name is the name you give to the count results.
• your_table_name is the name of the table from which you want to get the count.

Get more understanding of the concept by observing a few examples below. We will be looking into three examples to get the different count from table students_data based on conditions.

Example 1: Get the count of subjects associated with Grade A

Observe the below query for the solution.

SELECT DISTINCT
    COUNT(DISTINCT IF(student_grade = 'A',
            student_subject,
            NULL)) AS countOfSubjects
FROM
    students_data;

Output:-

Explanation:-

The output shows that there are three distinct subjects where the students got the grade ‘A.’

Here, we are applying the count() function on students_subjects column by calculating count of distinct subjects if the student_grade = ‘A’. In case the grade is other than ‘A’ in that case the subject count will not be calculated.

Example 2: Get the count of students who got Grade A, Grade B, GRADE C, and Grade D, respectively.

Observe the below query for the solution.

SELECT 
    COUNT(DISTINCT student_id) AS TotalStudents,
    COUNT(DISTINCT IF(student_grade = 'A',
            student_id,
            NULL)) AS A_GradeHolders,
    COUNT(DISTINCT IF(student_grade = 'B',
            student_id,
            NULL)) AS B_GradeHolders,
    COUNT(DISTINCT IF(student_grade = 'C',
            student_id,
            NULL)) AS C_GradeHolders,
    COUNT(DISTINCT IF(student_grade = 'D',
            student_id,
            NULL)) AS D_GradeHolders
FROM
    students_data;

Output:-

Explanation:-

Here we are getting the number of students who got respective grades.

In the count() function, we are checking the condition If student_grade = ‘A’/’B’/’C’/D’ If yes, only then get the number of students by calculating count based on student_id column.

Example 3: How many students with subject Physics are A Grade holders

Observe the below query for the solution.

SELECT DISTINCT
    COUNT(DISTINCT IF(student_subject = 'Physics',
            IF(student_grade = 'A',
                student_id,
                NULL),
            NULL)) AS countOf_GradeA_PhysicsStudents
FROM
    students_data;

Output:-

Explanation:-

The output shows that there are two physics students with Grade A.

In this query, we are using the nested IF() function. Here in the first IF() condition, we are checking if the student_subject = ‘Physics’ if YES, then check another IF() condition student_grade = ‘A’ if YES only then get the number of students by calculating count on student_id column.

We hope this article helped you to understand how to use COUNT() with IF(). Good Luck!!!