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?

SQL Count
2022-03-17 21:23:22

SQL Count

Structured Query Language Count() Function is used with Structured Query Language SELECT Statement. SQL Count() function returns the number of items that match the specified criteria in the SELECT statement.

Count() function includes Duplicates values also. If you want to discard the duplicate value count, use the DISTINCT keyword with the Count() function.

Syntax

Here is the syntax of the Count function in Structured Query Language.

SELECT COUNT(*) FROM TABLE_NAME;

And this syntax will return the total number of records present inside the table.

SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;

We have to mention the column name after the COUNT keyword and the table name in this syntax.

Example

We consider the existing Employees table with certain columns as an example of Count Functions in Structured Query Language.

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDWORKING_HOURSGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE112F
1002VAIBHAVSHARMA60000NOIDAORACLE19M
1003NIKHILVANI50000JAIPURFMW210M
2001PRACHISHARMA55500CHANDIGARHORACLE110F
2002BHAVESHJAIN65500PUNEFMW212M
2003RUCHIKAJAIN50000MUMBAITESTING49F
3001PRANOTISHENDE55500PUNEJAVA39F
3002ANUJAWHERE50500JAIPURFMW29F
3003DEEPAMJAUHARI58500MUMBAIJAVA312M
4001RAJESHGOUD60500MUMBAITESTING410M
  • Write a query to count the total records from the Employees table.
SELECT COUNT(*)  AS TOTAL EMPLOYEES FROM EMPLOYEES;

The above query shows the following result it means there is a total of 10 records in the Employees table.

SQL COUNT
  • Write a query to count the total department number from the Employees table.
SELECT COUNT(DEPARTMENT) AS TOATLDEPARTMENT FROM EMPLOYEES;

The above query shows the following result. It included duplicate department values also.

SQL COUNT

Suppose you want to count unique values from the department field, then you will use DISTINCT Keywords inside the Count() function with Column Name.

  • Write a query to count the unique department of employees from the Employees table.
SELECT COUNT(DISTINCT DEPARTMENT) AS DEPARTMENT FROM EMPLOYEES;

The above query shows the following result. It shows only 4 counts because there are only 4 unique departments in the Employees table.

SQL COUNT
  • Write a query to count unique cities from the employee's table.
SELECT COUNT(DISTINCT CITY) AS CITY FROM EMPLOYEES;

The above query shows the following result. It shows only 5 counts because there are only 5 unique cities in the Employees table.

SQL COUNT
  • Write a query to count the total number of those employees who belong to city Pune.
SELECT COUNT(*) FROM EMPLOYEES WHERE CITY = ‘PUNE’;

Above query shows the following result:

SQL COUNT

There are a total of 3 employees who are from Pune city.

  • Write a query to count the total number of employees whose first name starts with 'V' and salary is greater than 50000.
SELECT COUNT(*) FROM EMPLOYEES WHERE FIRST_NAME  LIKE ‘V%’ AND SALARY > 50000;

Above query shows the following result:

SQL COUNT

There are only 2 employees whose first name starts with V and salary is greater than 50000.

  • Write a query to count the total numbers of the department from the employee's table joined with the manager table.
SELECT COUNT(DEPARTMENT) AS DEPARTMENT FROM EMPLOYEES INNER JOIN MANAGER ON EMPLOYEES.MANAGERID = MANAGER.MANAGER;

Above query shows the following result:

SQL COUNT

The above query return counts a total number of departments whose manager id is present in the employee's table from the manager table.