MySQL - COUNT() Function


Advertisements

In general, aggregation is a consideration of a collection of objects that are bound together as a single entity.

MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit.

The MySQL COUNT() function is used to calculate the number of non-NULL values in a particular column. If the specified row(s) doesn’t exist this function returns 0.

If you invoke this function as COUNT(*) this function returns the number of records in the specified table irrespective of the NULL values.

Syntax

Following is the syntax of this function –

COUNT(expr);

Example 1

Following is an example demonstrating the usage of this function. Assume we have created a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Socre_In_Exhibiiton_match INT,
	COUNTRY VARCHAR(100),
	PRIMARY KEY (ID)
);

This table stores the first and last names, country, scores in an exhibition match of a player. Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', 95, 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', 50, 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', 25, 'Sri Lanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', 50, 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', 25, 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', 15, 'India'); MySQL COUNT() Function
insert into MyPlayers values(7, 'James', 'Anderson', 15, 'England');

Following query returns the number of records in the table –

mysql> SELECT COUNT(*) from MyPlayers;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

You can also use the GROUP BY clause along with this function –

mysql> SELECT First_Name, Last_Name, COUNTRY, COUNT(*) FROM MyPlayers GROUP BY country;
+------------+------------+-------------+----------+
| First_Name | Last_Name  | COUNTRY     | COUNT(*) |
+------------+------------+-------------+----------+
| Shikhar    |     Dhawan |       India |        4 |
| Jonathan   |      Trott | SouthAfrica |        1 |
| Kumara     | Sangakkara |    Srilanka |        1 |
| James      |   Anderson |     England |        1 |
+------------+------------+-------------+----------+
4 rows in set (0.07 sec)

Example 2

Following is another example of this function. Assume we have created another table named employee_tbl and inserted records in it as follows –

mysql> CREATE TABLE employee_tbl (
	id INT,
	name VARCHAR(255),
	Work_date INT,
	daily_typing_pages INT
);
mysql> insert into employee_tbl values(1, 'John', DATE('2007-01-24'), 250);
mysql> insert into employee_tbl values(2, 'Ram', DATE('2007-05-27'), 220);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-05-06'), 170);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-04-06'), 100);
mysql> insert into employee_tbl values(4, 'Jill', DATE('2007-04-06'), 220);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-06-06'), 300);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-02-06'), 350);

Now, suppose based on the above table you want to count total number of rows in this table, then you can do it as follows −

mysql> SELECT COUNT(*) FROM employee_tbl;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

Similarly, if you want to count the number of records for Zara, then it can be done as follows −

mysql> SELECT COUNT(*) FROM employee_tbl WHERE name = "Zara";
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.02 sec)

Example 3

Let us create a table named student and inserted records into it using CREATE and INSERT statements as shown below –

mysql> CREATE TABLE student (name VARCHAR(15), marks INT, grade CHAR);
mysql> INSERT INTO student VALUES ('Raju', NULL, 'A');
mysql> INSERT INTO student VALUES ('Rahman', 60, 'B');
mysql> INSERT INTO student VALUES ('Robert', 45, 'C');

Following query prints the number of records that does have the marks values in student table –

mysql> SELECT COUNT(marks) from student;
+--------------+
| COUNT(marks) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

Example 4

Assume we have created and populated a table with name Sales.

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DeliveryTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)
INSERT INTO SALES values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
INSERT INTO SALES values(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
INSERT INTO SALES values(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
INSERT INTO SALES values(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
INSERT INTO SALES values(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query is another example of the COUNT() function –

mysql> SELECT COUNT(Price) FROM SALES WHERE Price >5000;
+--------------+
| COUNT(Price) |
+--------------+
|            3 |
+--------------+
1 row in set (0.12 sec)
mysql-aggregate-functions.htm
Advertisements