MySQL select row count [Everything around count()]

Count() function counts the number of rows. In this article, we will talk about how to get the row count in MySQL for different requirements, particularly:

1. Count table rows
2. Select row count of a query
3. Select row count(distinct)
4. Select row count GROUP BY
5. MySQL select row count from all tables
6. How to get the number of rows that a MySQL query returned
7. Count(*) from multiple tables in MySQL

Let us start with creating the data to be used across.

• Create the table customer_sale_details.
• Insert the data to the table customer_sale_details.
• Select from customer_sale_details to view the data.

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)
);
 INSERT INTO customer_sale_details (customer_name,no_products_purchased,date_of_purchase) 
 VALUES
 ("Henric",56,'2020-11-05 14:29:36'),
 ("Rasmus",23,'2020-12-06 15:29:36'), 
 ("Henry",2,'2020-12-12 14:30:36'),
 ("George",5,'2020-10-12 14:39:45'),
 ("Richa",45,'2021-01-01 13:29:26'),
 ("Ved",38, '2021-01-02 14:40:36'),
 ("Erick",200,'2021-01-02 12:30:30'),
 ("Mounika",12,'2020-10-05 11:19:36'),
 ("Tanya",15,'2019-11-05 12:31:31'),
 ("Abrahim",16,'2019-11-05 11:39:36'),
 ("Henric",139,'2021-01-07 10:10:16'),
 ("George",156,'2021-01-07 11:11:33');
SELECT * FROM customer_sale_details;

Output:-

Count table rows

In this section, let us see how to get the number of rows in the table. Observe the below query to get the count of records in customer_sale_details table.

SELECT COUNT(*) FROM customer_sale_details ;

Output:-

This output shows that the table customer_sale_details has 12 rows.

Select row count of a query

Let us see in this section how to get the number of rows returned by the query. The below query is to get the count of rows from customer_sale_details table where customer_name = “George”.

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

Output:-

The output shows that the count of rows returned from the query is 2.

Select row count(distinct)

At times there is a requirement to get the count of distinct values within a query or a sub-query. Observe the below query to get the distinct customer names from customer_sale_details table.

SELECT COUNT(DISTINCT customer_name) AS Customer_Names FROM customer_sale_details;

Output:-

Let us take another example and observe the below query to get the distinct customer_name from customer_sale_details table who purchased less than 100 products.

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

Output:-

Select row count GROUP BY

When used with GROUP BY, the count() function will get the number of records characterized under various groupings. Let us see how to use GROUP BY with the count() function in the below example. Get the 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 select row count from all tables

This section will see different ways to get the count of all the tables within a 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:-

The below query will get the count of all the tables which are present in the schema riti_sales_DB.

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

Output:-

Another way to get the count of all tables is by SHOW STATUS.

SHOW TABLE STATUS;

SHOW STATUS, along with other information, will also get the number of rows in each table, as shown in figure 8.

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

What if we want to know how many rows were returned by the query, we ran last ? An easy option is to run the SELECT FOUND_ROWS(), which will return the number of rows that existed in the previous query. Notice the below query and its output.

SELECT * FROM customer_sale_details WHERE no_products_purchased < 50;

Output:-

In the output, we got 8 rows. If we want to review the count of rows returned from the last query, execute the FOUND_ROWS() with SELECT.

SELECT FOUND_ROWS();

Output:-

Select count(*) from multiple tables

In this section, we will see how to get the count from more than one table. We will be using tables customer_sale_details and another existing table customer_details. Select * from customer_details shows the below output.

To get the count(*) from both the tables, notice the two different ways:

Using UNION :

  SELECT COUNT(*) FROM customer_sale_details 
  UNION
  SELECT COUNT(*) FROM customer_details ;

Output:-

Using Sub-queries :

SELECT (SELECT COUNT(*) FROM customer_details WHERE customer_name="Henric") AS CountFromFirstTable, 
       (SELECT COUNT(*) FROM customer_sale_details WHERE customer_name="Henric") AS CountFromSecondTable ;

Output:-

We have tried to put across many different ways to show the count() function usage. We hope this article will be helpful while working with the COUNT() function. Good Luck !!!