Summary -

In this topic, we described about the Select Count with detailed example.

SELECT COUNT used to get total number of rows from a table based on various scenarios. SELECT COUNT returns the number of rows matched with the condition in WHERE clause.

Syntax for SELECT COUNT –

 SELECT COUNT(column_name) FROM Table_name [WHERE Condition];  
  • column_name – Specifies the name of the column on which we perform COUNT.
  • Table_name- Specifies the table name.

Syntax for creating user defined name -

SELECT COUNT (column_name) as user_defined_name from Table_name ; 
  • user_defined_name – Specifies the temporary name of the result column.
  • Table_name – Specifies the name of the table.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario - Get total number of rows in a table.

Requirement – get total number of rows in employee_details table. The query was as follows-

SELECT COUNT(*)  AS 'Total employees' FROM employee_details; 

By executing above query, we can get the total number of rows of employee_details table. The output was as shown in below -

Total employees
9

Scenario- Get the number of rows with WHERE clause and define the result with user defined name.

Requirement – Get the number of rows that are from department 1000 in employee_details table. The query was as follows-

SELECT COUNT(*)  AS 'Total employees' 
FROM employee_details 
WHERE dept_id = 1000; 

By executing above query, we can get the count of employees those are belong to department 1000. The output was as shown in below -

Total employees
5

Scenario- Get the total number of rows in a table by using with DISTINCT.

Requirement – Get the unique designation count from employee_details table. The query was as follows-

SELECT COUNT(DISTINCT designation) AS 'Total designations'
FROM employee_details; 

By executing above query, we can get unique designations count from the employee_details table. The output was as shown in below -

Total designations
5