MySQL row count – MySQL select row count [Everything around count()]

by

MySQL row count: In this article we are going to discuss MySQL select row count or Count() function.

Now we are going to create a table ‘customer_sale_details’ which we will be using in our article to show row count() function.

CREATE TABLE customer_sale_details (
    customer_id INT auto_increment,
    customer_name VARCHAR(255),
    no_products_purchased INT,
    date_of_purchase DATETIME,
    primary key (customer_id)
);

Now we will insert value in it,

INSERT INTO customer_sale_details (customer_name,no_products_purchased,date_of_purchase) 
 VALUES
 ("Hari",12,'2020-11-05 14:29:36'),
 ("David",43,'2020-12-06 15:29:36'), 
 ("Isha",6,'2020-12-12 14:30:36'),
 ("Kiya",8,'2020-10-12 14:39:45'),
 ("Liyo",56,'2021-01-01 13:29:26'),
 ("Jay",13, '2021-01-02 14:40:36'),
 ("Oam",134,'2021-01-02 12:30:30'),
 ("Mounika",183,'2020-10-05 11:19:36'),
 ("Tanya",13,'2019-11-05 12:31:31'),
 ("Arya",13,'2019-11-05 11:39:36'),
 ("Hary",145,'2021-01-07 10:10:16'),
 ("Goyal",45,'2021-01-07 11:11:33');

Output:

MySql row count

Count table rows

Here we will discuss how to count number of rows in MySQL table.Below is the query for it,

SELECT COUNT(*) FROM customer_sale_details ;

Output:

+———-+
| COUNT(*) |
+———-+
| 12 |
+———-+

Here in above output you can see that there is 12 rows in our table.

Select row count of a query

Here we are give a condition based on that it will return row number.See the below query,

SELECT count(*) FROM (SELECT * FROM customer_sale_details WHERE customer_name="George") AS count_of_query;

Output:

+———-+
| count(*) |
+———-+
| 1 |
+———-+

Select row count(distinct)

Here we are going to get distinct customer name from the table.See below query,

SELECT COUNT(DISTINCT customer_name) AS Customer_Names FROM customer_sale_details;

Output:

+—————-+
| Customer_Names |
+—————-+
| 12 |
+—————-+

Let’s take some other condition to show distinct customer_name from the table.

SELECT COUNT(customer_name) FROM (
    SELECT DISTINCT customer_name FROM customer_sale_details  WHERE no_products_purchased  < 100
) AS customer_count;

Output:

———————-+
| COUNT(customer_name) |
+———————-+
| 9 |
+———————-+

Select row count GROUP BY

Here we are going to show working of GROUP BY using COUNT() function in it.Now we will get number of records GROUPED BY customer_name,

SELECT 
    customer_name, COUNT(customer_name) AS records_per_customer
FROM
    customer_sale_details
GROUP BY customer_name

Output:

MySql row count1

MySQL select row count from all tables

In this we are going to discuss different ways to get row count of all table present in database schema.

Syntax:

SELECT 
    TABLE_NAME, SUM(TABLE_ROWS) AS COUNT_OF_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'Name of your schema'
GROUP BY TABLE_NAME;

Example:

Here we are going to get the count of all the tables which are present in the schema ‘abc’.

SELECT 
    TABLE_NAME, SUM(TABLE_ROWS) AS COUNT_OF_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'abc'
GROUP BY TABLE_NAME;

Output:

MySql row count2

 

You can also see status by using below query;

SHOW TABLE STATUS;

How to get the number of rows that a MySQL query returned

Here we are going to use SELECT FOUND_ROWS() which will return previous query rows.

SELECT * FROM customer_sale_details WHERE no_products_purchased < 50;

Output:

MySql row count3

You can see that in output  we have got 8 rows.If you want to see the returned rows from last query you can get by using below query,

SELECT FOUND_ROWS();

Output:

+————–+
| FOUND_ROWS() |
+————–+
| 8 |
+————–+

Conclusion:

In this article we have discussed in deep MySQL select row count or Count() function.Thank You!