Select count(*) with distinct

In this article, we will be discussing how to get the distinct count of rows and distinct count of values for a column from a table in MySQL.

Table of Contents

• Select count(*) with DISTINCT syntax
• Select count(*) with DISTINCT examples
• Count DISTINCT values in a column
  

Count(*) function in MySQL is used to get the number of rows associated with the query. In case we want to get the count of different values we can use COUNT(*) with DISTINCT.

Let us get started by making the data. We will be creating a table student_info and adding data to it to be used across.

CREATE TABLE student_info (
student_id INT ,
student_name VARCHAR(255),
student_grade VARCHAR(50),
student_subject VARCHAR(50),
reference_city VARCHAR(255)
);
INSERT INTO student_info (student_id, student_name, student_grade, student_subject, reference_city) 
VALUES(1,"Gustav","A","Physics","Santiago"),
(2,"Henric","B","Physics","Santiago"),
(3,"Richa","C","Physics","Chicago"),
(4,"Margit","A","Physics","Seattle"),	
(5,"Rasmus","B","Physics","Dallas"),
(6,"Erick","C","Physics","Chicago"),
(1,"Gustav","A","Geography","Chicago"),
(2,"Henric","B","Geography","Santiago"),
(3,"Richa","B","Geography","Dallas"),
(4,"Margit","A","Geography","Chicago"),	
(5,"Rasmus","B","Geography","Seattle"),
(6,"Erick","A","Geography","Chicago"),
(1,"Gustav","B","Chemistry","Seattle"),
(2,"Henric","B","Chemistry","Santiago"),
(3,"Richa","D","Chemistry","Dallas"),
(4,"Margit","A","Chemistry","Santiago"),	
(5,"Rasmus","A","Chemistry","Seattle"),
(6,"Erick","D","Chemistry","Santiago"),
(1,"Gustav","B","Computers","Chicago"),
(2,"Henric","A","Computers","Santiago"),
(3,"Richa","A","Computers","Santiago"),
(4,"Margit","A","Computers","Santiago"),	
(5,"Rasmus","C","Computers","Seattle"),
(6,"Erick","A","Computers","Santiago"); 

Let us see what got into table student_info by executing:

SELECT * FROM student_info ORDER BY student_id ;

Action Output Message Response:-

24 row(s) returned

Output:-

Select count(*) with DISTINCT syntax

Syntax for COUNT with DISTINCT:

SELECT DISTINCT column_name, COUNT(*) AS alias_name FROM table_name WHERE <conditions> GROUP BY column_name;

• Here, column_name is your column’s name with which you want to form groups and have it displayed as a part of the result set.
• table_name is the name of your table from where we get the data.
• conditions are the constraint you specify in the WHERE clause.
• The DISTINCT keyword is used after SELECT and before the column_name.

Select count(*) with DISTINCT examples

Let us forge ahead by looking into examples. We will be using an example to explain COUNT(*) with DISTINCT.

Example 1:-

Get the count of subjects for each student where they got GRADE A.

Observe the below query for the solution.

SELECT DISTINCT
    student_name, COUNT(*) AS count_of_subjects
FROM
    student_info
WHERE
    student_grade = 'A'
GROUP BY student_name;

Action Output Message Response:-

6 row(s) returned.

Output:-

The output in figure 2 shows the number of subjects for which each student got a Grade ‘A.’ For example, Margit got a Grade of ‘A’ in 4 subjects.

Example 2:-

Get the count of students who got Grade ‘A” from each city.

Observe the below query for the solution.

SELECT DISTINCT
    reference_city, COUNT(*) AS countOfStudents
FROM
    student_info
WHERE
    student_grade = 'A'
GROUP BY reference_city;

Action Output Message Response:-

3 row(s) returned.

Output:-

The output in figure 3 shows number of students from each city who got Grade ‘A’ for example there were 6 students from Santiago who got Grade ‘A’.

Count DISTINCT values in a column

This section will see how to get the count of different values from a column in a table.

Syntax:-

SELECT COUNT(DISTINCT column_name) FROM table_name WHERE <conditions>;

• Here, column_name is the name of your column for which you want to get different values.
• table_name is the name of your table from where we get the data.
• conditions are the constraint you specify in the WHERE clause.
• DISTINCT is used within the COUNT() function.

Example:-

To have a better understanding of the concept, let us look into an example.

Get the distinct number of students who got at least one Grade ‘A.’

Observe the below query for the solution.

SELECT 
    COUNT(DISTINCT student_id) AS count_of_top_graders
FROM
    student_info
WHERE
    student_grade = 'A';

Figure 4 shows that 6 different students got Grade ‘A’ in at least one subject.

We hope this article helped you to have an understanding of the concept of COUNT WITH DISTINCT. Good Luck !!!

LEARN MORE ON COUNT:

• COUNT WITH IF CONDITION
• GET MULTIPLE COUNTS IN SINGLE MySQL QUERY
• MySQL SELECT ROW COUNT
• MySQL SELECT WHERE ROW COUNT IS GREATER THAN ONE