SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

How to use COUNT in SQL?
2021-05-26 13:59:38

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

  1. 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)
How to use COUNT in SQL?
mysql> SELECT COUNT(*) FROM employee;

Output:

 +----------+
 | COUNT(*) |
 +----------+
 |        7 |
 +----------+
 1 row in set (0.00 sec)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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)
How to use COUNT in SQL?

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.