How to use COUNT in SQL?
How to use COUNT in SQL
Introduction
- COUNT( ) is an aggregate function in SQL.
- This function counts the number of records in a table if the condition is not specified.
- If the condition is specified, the count function returns the number of records satisfying the specified condition.
Variations of COUNT ( ) in SQL
- COUNT(*)
COUNT (*) is used to display the number of records present in a table.
The results of COUNT (*) function will contain NULL as well as duplicate entries also into consideration.
Syntax:
SELECT COUNT (*) FROM tablename;
Example:
First we will create a database with name “employeedb”. Then in that database we will create a table “employee” and insert records into the table. We will consider this table and database for all the subsequent examples.
We will find the number of records present in ‘employee’ table using COUNT () function.
mysql> USE employeedb; Database changed mysql> SELECT *FROM employee; +--------+----------+------------+ | Emp_ID | Emp_Name | Emp_Salary | +--------+----------+------------+ | 1 | Nikita | 30000 | | 2 | Riddhi | 25000 | | 3 | Nayan | 45000 | | 4 | Shruti | 15000 | | 5 | Anurati | 55000 | | 6 | NULL | NULL | | 7 | Anurati | 55000 | +--------+----------+------------+ 7 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM employee;
Output:
+----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec)
There are 7 records present in employee table.
- COUNT (1)
COUNT (1) is also used to display the number of records present in a table. The results of COUNT (1) function will contain NULL as well as duplicate entries also into consideration. COUNT (1) function works same as COUNT (*). Even the results of COUNT (1) and COUNT (*) are also same.
Syntax:
SELECT COUNT (1) FROM tablename;
Example 1: We will display the number of records present in employee table using COUNT (1).
mysql> SELECT COUNT(1) FROM employee;
Output:
+----------+ | COUNT(1) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec)
There are 7 records present in a table.
Example 2:
Let us see what happens when we pass 14 as a parameter to COUNT() function.
mysql> SELECT COUNT(14) FROM employee;
Output:
+-----------+ | COUNT(14) | +-----------+ | 7 | +-----------+ 1 row in set (0.00 sec)
There are 7 records present in a table. So, 7 is displayed as an output even though we have passed 14 as a parameter to COUNT() function. Passing an integer to a COUNT() function does not mean to count those number of rows in table. It simply means 14 will be assigned to each and every row present in a table and then that rows will be summed up to give a total and displayed as an output.
Example 3:
Let us see what happens when we pass -14 as a parameter to COUNT() function.
mysql> SELECT COUNT(-14) FROM employee;
Output:
+------------+ | COUNT(-14) | +------------+ | 7 | +------------+ 1 row in set (0.07 sec)
There are 7 records present in a table. So, 7 is displayed as an output even though we have passed -14 as a parameter to COUNT() function. It simply means -14 will be assigned to each and every row present in a table and then that rows will be summed up to give a total and displayed as an output.
- COUNT(ColumnName)
COUNT(ColumnName) is used to find the number of records which contain values for the specified column. While using COUNT() function with columnname as a parameter, the records which contain NULL values for that record will be ignored.
Syntax:
SELECT COUNT(ColumnName) FROM tablename;
Example 1:
We will display the number of records which exists for Emp_ID.
mysql> SELECT COUNT(Emp_ID) FROM employee;
Output:
+---------------+ | COUNT(Emp_ID) | +---------------+ | 7 | +---------------+ 1 row in set (0.00 sec)
There are 7 records which contain unique Emp_ID. Therefore, 7 is displayed as an output.
Example 2:
We will display the number of records which exists for Emp_Name.
mysql> SELECT COUNT(Emp_Name) FROM employee;
Output:
+-----------------+ | COUNT(Emp_Name) | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec)
There are 7 records in employee table among which, one record contain NULL values for Emp_Name. So, that particular record is ignored and 6 is displayed as an output.
Example 3:
We will display the number of records which exists for Emp_Salary.
mysql> SELECT COUNT(Emp_Salary) FROM employee;
Output:
+-------------------+ | COUNT(Emp_Salary) | +-------------------+ | 6 | +-------------------+ 1 row in set (0.00 sec)
There are 7 records in employee table among which one record contain NULL values for Emp_Salary. So, that particular record is ignored and 6 is displayed as an output.
- COUNT(DISTINCT ColumnnName)
COUNT() function with DISTINCT ColumnName as its parameter is used to display the number of records which contains unique values for a specific column. Records which contain duplicate and NULL values will not be counted.
Syntax:
SELECT COUNT(DISTINCT ColumnName) FROM tablename;
Example 1:
We will display the number of records which contain unique values for Emp_ID.
mysql> SELECT COUNT( DISTINCT Emp_ID) FROM employee;
Output:
+-------------------------+ | COUNT( DISTINCT Emp_ID) | +-------------------------+ | 7 | +-------------------------+ 1 row in set (0.05 sec)
There are 7 records which contain unique values for Emp_ID.
Example 2:
We will display the number of records which contain unique values for Emp_Name.
mysql> SELECT COUNT( DISTINCT Emp_Name) FROM employee;
Output:
+---------------------------+ | COUNT( DISTINCT Emp_Name) | +---------------------------+ | 5 | +---------------------------+ 1 row in set (0.00 sec)
There are 5 records which contain unique values for Emp_Name. NULL and duplicate values in Emp_Name will not be considered by the DISTINCT keyword.
Example 3:
We will display the number of records which contain unique values for Emp_Salary.
mysql> SELECT COUNT( DISTINCT Emp_Salary) FROM employee;
Output:
+-----------------------------+ | COUNT( DISTINCT Emp_Salary) | +-----------------------------+ | 5 | +-----------------------------+ 1 row in set (0.00 sec)
There are 5 records which contain unique values for Emp_Salary. NULL and duplicate values in Emp_Salary will not be considered by the DISTINCT keyword.