SQL Count Rows query

<<Previous - SQL MAX() Function

Next - SQL AVG() Function >>

SQL COUNT

SQL COUNT() function counts the number of rows in the selected column.

SQL Syntax

SELECT COUNT (column-name) FROM  Table-name
WHERE condition;

SQL COUNT Example

Demo student_mark table from collegedb database.

mysql> SELECT * FROM Student_Mark;
+--------+---------+-----------+------+
| RollNo | Name    | City      | Mark |
+--------+---------+-----------+------+
|    101 | Sakthi  | Chennai   |   80 |
|    102 | Bala    | Chennai   |   86 |
|    103 | Chandra | Bangalore |   89 |
|    104 | Madhan  | Goa       |   80 |
|    105 | Jose    | Kerala    |   82 |
|    106 | Jithu   | Kerala    |   85 |
|    107 | Veni    | Bangalore |   85 |
+--------+---------+-----------+------+
7 rows in set (0.00 sec)

Following query statement counts number of rows in the selected column

mysql> SELECT COUNT(City) FROM Student_Mark;
+-------------+
| COUNT(City) |
+-------------+
|           7 |
+-------------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(Distinct City) FROM Student_Mark;
+----------------------+
| count(distinct City) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.03 sec)

Note : To remove the duplicate values,we use Distinct keyword.

<<Previous - SQL MAX() Function

Next - SQL AVG() Function>>