COUNT Function in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

COUNT Function in MySQL with Examples

In this article, I am going to discuss the COUNT Function in MySQL with Examples. Please read our previous article where we discussed Constraints in MySQL with Examples.

Functions in MySQL

There are many pre-defined functions in SQL to perform various actions on data rows. Some functions work only on numeric values while some function works on numeric as well as string values. There are mainly 2 types of functions in SQL, Aggregate Functions and Scalar Functions.

1. Aggregate Functions only work on a group of rows to return a single aggregate result value. The Example of Aggregate Functions are: COUNT(), MIN(), MAX(), SUM(), ANG(), etc.
2. Scalar Functions work on single input value to return single result value. The Example of Scalar Functions are: LEN(), ROUND(), SUBSTRING(), CASE(), NOW(), etc.

MySQL COUNT() Function:

The COUNT function is used to count the data rows returned in the result set. MySQL COUNT function counts distinct or all values in data rows returned in a result set. The COUNT function does not count NULL values.

Syntax:
SELECT COUNT(*) AS total_rows FROM tablename;
SELECT COUNT (DISTINCT columnname) AS total_rows FROM tablename;

Example to Understand MySQL Count Function

We are going to use the following Employee and Projects table to understand the MySQL Count function with Examples.

Please use the following SQL Script to create and populate the Employee and Projects table with the required sample data.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO Employee VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

CREATE TABLE Projects (
 ProjectId INT PRIMARY KEY,
    	Title VARCHAR(200) NOT NULL,
    	ClientId INT,
 EmployeeId INT,
    	StartDate DATETIME,
    	EndDate DATETIME,
    	FOREIGN KEY (EmployeeId) REFERENCES Employee(Id)
);

INSERT INTO Projects VALUES (1, 'Develop ecommerse website from scratch', 1, 1003, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY));
INSERT INTO Projects VALUES (2, 'WordPress website for our company', 1, 1002, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY));
INSERT INTO Projects VALUES (3, 'Manage our company servers', 2, 1007, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY));
INSERT INTO Projects VALUES (4, 'Hosting account is not working', 3, 1009, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY));
INSERT INTO Projects VALUES (5, 'MySQL database from my desktop application', 4, 1010, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY));
INSERT INTO Projects VALUES (6, 'Develop new WordPress plugin for my business website', 2, NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY));
INSERT INTO Projects VALUES (7, 'Migrate web application and database to new server', 2, NULL, NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY));
INSERT INTO Projects VALUES (8, 'Android Application development', 4, 1004, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY));
INSERT INTO Projects VALUES (9, 'Hosting account is not working', 3, 1001, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY));
INSERT INTO Projects VALUES (10, 'MySQL database from my desktop application', 4, 1008, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY));
INSERT INTO Projects VALUES (11, 'Develop new WordPress plugin for my business website', 2, NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY));

Example: Count the Total Number of employees in the Employee table.

Now, let’s count the data rows in the Employee table using the COUNT function in MySQL by executing the below SQL Statement.

SELECT COUNT(*) FROM Employee;

Once you execute the above SQL Statement, you will get the following output. As the employee table contains 10 rows, so here you will get the output as 10.

COUNT Function with Alias Name in MySQL

We can also use Alias to give the name to the data column. By default, the column name would be COUNT(*) that you can see in the previous example. Instead of COUNT(*), we want to provide the name as Total_Employees. Then we can do the same by using the ALIAS name as shown in the below SQL Statement.

SELECT COUNT(*) AS Total_Employees FROM Employee;

Now execute the above statement and you will get the following output. Notice, now the column name is Total_Employees instead of COUNT(*).

Count Distinct Values in MySQL

We already know we have 3 distinct values in the Department column of the Employee table. Let us get the number of employees in each department by using the COUNT function. Here, we also need to use the group by clause to group the employees by department and then apply the count function on each department to get the desired result. Following is the SQL Script.

SELECT Department, COUNT(*) AS Total_Employees 
FROM Employee
GROUP BY Department;

Once you execute the above query, you will get the following output. As you can see, the above SQL statement will get only the distinct values from the column name department and its total number of appearances on the employee table.

MySQL COUNT Function with Joins

As we already learned about LEFT OUTER JOIN. By using left outer join we get matching records from both the tables as well as non-matching records from the left-hand side table. Let us first left join, the Employee and Projects table as executing the below SQL Statement.

SELECT emp.Id AS EmployeeId, emp.Name, prj.Title AS ProjectTitle 
FROM Employee AS emp 
LEFT OUTER JOIN Projects AS prj 
ON emp.Id = prj.EmployeeId;

Now execute the above query, and you will get the following data rows.

As you can see in the above image, we got 10 rows but in this case, you can only 8 data rows of the title column have a string value while the remaining data row title column has NULL value mean no value.

As we’ve already learned in this article, The COUNT function does not count NULL values. If we try to count the total data rows in the title column, using the SQL statement, it should only return the 8. Let us prove this by executing the below SQL Statement.

SELECT COUNT(prj.Title) AS TotalProject 
FROM Employee AS emp 
LEFT OUTER JOIN Projects AS prj 
ON emp.Id = prj.EmployeeId;

Now when you execute the above SQL statement, and you will get the following output which shows the count as 8. That is from this title and the NULL values are escaped from this count function.

In the next article, I am going to discuss MySQL SUM Function with Examples. Here, in this article, I try to explain the COUNT Function in MySQL with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.