MySQL - COUNT() Function



The MySQL COUNT() function is used to calculate the count of non-NULL values in a particular column. If the specified row(s) don't exist in the table, this function returns 0.

If we 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 MySQL COUNT() function −

COUNT(expr);

Parameters

This method accepts a parameter. The same is described below −

  • expr: A column name or a string value.

Return value

This function returns the number of rows that match the specified condition. If no rows match the condition, it returns 0.

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, we are using the MySQL COUNT() function to count the number of records in the table −

SELECT COUNT(*) From CUSTOMERS;

Output

Following are the number of records in the CUSTOMERS table −

COUNT(*)
7

Example

Consider the previously created CUSTOMERS table and use the GROUP BY clause along with the COUNT() function to return the count of age of customers −

SELECT AGE, COUNT(*) FROM CUSTOMERS 
GROUP BY AGE;

Output

The output for the query above is produced as given below −

AGE COUNT(*)
32 1
25 2
23 1
27 1
22 1
24 1

Example

Here, we are counting the number of rows in the 'CUSTOMERS' table where the 'SALARY' is greater than 5000 −

SELECT COUNT(SALARY) FROM CUSTOMERS WHERE SALARY > 5000;

Output

This will produce the following result −

COUNT(SALARY)
3
Advertisements