SQL SELECT COUNT

In this SQL tutorial, you are going to learn how to use SQL SELECT COUNT Function along with syntax and examples.

SQL COUNT Function is used to find the number of record returned in the result set.

This function is used along with the SQL SELECT statement and extremely useful when the data in a table is huge.

For example, if you need find number of students admitted for a particular university in a particular admission season, then it is difficult to find out by manual process. You can easily find the result using SQL SELECT COUNT statement.

Syntax

The basic syntax of SQL SELECT COUNT function is as below.

SELECT COUNT(expression) 
FROM tables
[WHERE conditions];

In this syntax,

• expression – name of the column on which count operation will be performed. Only NOT NULL values will be counted in this case.
• tables – The name of tables from which you want fetch the records. These should be at least one table listed in the FROM clause.
• WHERE conditions – Optional. The conditions should be met in order to return the records.

SQL SELECT COUNT Examples

In order to understand the SQL COUNT() function, lets take an example of student table having following records.

SQL> select * from student;
+----+-------------+-------+------+--------+
| id | name | class | mark | gender |
+----+-------------+-------+------+--------+
| 1 | John Deo | Four | 75 | female |
| 2 | Max Ruin | Three | 85 | male |
| 3 | Arnold | Three | 55 | male |
| 4 | Krish Star | Four | 60 | female |
| 5 | John Mike | Four | 60 | female |
| 6 | Alex John | Four | 55 | male |
| 7 | My John Rob | Five | 78 | male |
| 8 | Asruid | Five | 85 | male |
| 9 | Tes Qry | Six | 78 | male |
| 10 | Big John | Four | 55 | female |
| 11 | Ronald | Six | 89 | female |
| 12 | Recky | Six | 94 | female |
+----+-------------+-------+------+--------+
12 rows in set (0.046 sec)

Now, suppose you want to count the total number of students, then you can use count() function as follows.

SQL> select count(*) from student;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.045 sec)

SQL SELECT COUNT(column_name)

Similarly, if you want to count total number of students in class four, then you can do it as follows.

SQL> select count(class) from student where class='four';
+--------------+
| count(class) |
+--------------+
| 5 |
+--------------+
1 row in set (0.046 sec)

NOTE:- All SQL queries are case insensitive, so it does not make any difference if you give ‘four’ or ‘Four’ in the SQL query.

SQL SELECT COUNT(DISTINCT column_name)

Now if you want to check how many class are there in the above example, you can get the desired result as follows.

SQL> select count(distinct class) from student;
+-----------------------+
| count(distinct class) |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.043 sec)