COUNT function returns the count of number of rows returned by a sql query.
Syntax to return the number of rows in a table:
SELECT COUNT(*) from table_name;
Consider the below employee table:
| employee_id | first_name | last_name | department |
| 101 | john | king | sales |
| 102 | bryan | walker | accounts |
| 103 | john | white | accounts |
| 104 | sam | lee | sales |
Below query returns the number of rows in the employee table:
SELECT count(*) from employee; count ----- 4
Below example returns the number of employees per department. Here employees are grouped by the department.
SELECT department, count(department) from employee group by department; department count ---------- ----- sales 2 accounts 2
Below query returns all the first_names that appears more than once. In this example count is used along with group by and having clauses.
SELECT first_name, count(first_name) from employee group by first_name having count(first_name)>1; first_name count ---------- ----- john 2
Below example shows the syntax to count distinct values within a column. In our employee table, 2 employees have first name as ‘John’. Thus while counting the distinct first names, ‘John’ is counted only once.
SELECT count(distinct first_name) from employee; count ----- 3
Below example shows the syntax to count distinct values over multiple columns. As you can see, no 2 employees in our employee table has same first_name and last_name. Thus the count returned is 4. However if there is another employee ‘sam lee’ with id 105, the below example would still return 4 as ‘sam lee’ will be returned only once by the sub query due to distinct clause.
SELECT count(*) from (SELECT distinct first_name, last_name from employee) as a; count ----- 4