MySQL - COUNT() Function

Quiz

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

Print Page