How to use Count in MySQL Query - MySQL Developer Tutorial

How to use Count in MySQL Query

Let's say if you need to get the count of all the rows from MySQL table, you can use COUNT Function.

Let's create our sample table customer and then insert some records before we run different scenarios for Count function in MySQL.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,null,'Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M');

Get Total Rows from a Table: 

To get the total row count from a table, you can use below syntax.

Select count(*) From YourTableName; 

Now if we want to get the row count from our customer table, we will use below query.

Select count(*) from customer;

MySQL Tutorial- Get Row Count by using Count function in MySQL

Get Count of values in Table Column : 

Let's say if you want to get the count of values in column of a table, you can use below syntax, Remember that it will ignore the Null values in the Count.

Select count(columnName) from YourTableName;

Let's say if want to get count of values in firstName in customer table, we can use below statement, As there is one Null value, the count will ignore that and will return only 3 out of 4 rows.

Select count(firstname) from customer;

Get Distinct Count : 

If you are interested to get Distinct ( Different) values count, you can use Distinct with Count function in MySQL.

In below example, if we want to get distinct values for firstname column in customer table. we can use below script. Notice that it will not consider the Null values in Count.

Select count(distinct firstname)  from customer;

MySQL Distinct Count from Column Table