devxlogo

Count the Number of Rows in Two Tables Using a Single Query

Count the Number of Rows in Two Tables Using a Single Query

Why Trust Us

There are several ways to simply count the number of rows in two tables using single query.

  1. select ( select count(*) from Table1 )     + ( select count(*) from Table2 )         as total_rows from my_one_row_table

  2.  select sum(rows)          as total_rows  from (    select count(*) as rows      from Table1    union all    select count(*) as rows      from Table2       ) as u

    UNION ALL is necessary here, not UNION, to guard against the case when both tables have the same number of rows, because UNION would discard one of the duplicate counts! Note also that no GROUP BY is necessary in the main query, because all the rows (produced by the UNION ALL subquery) are considered one group.

  3. We could also use a CROSS JOIN:
       select t1.rows + t2.rows            as total_rows    from (           select count(*) as rows           from Table1         ) as t1    cross join (            select count(*) as rows            from Table2               ) as t2

    The cross join works because each derived table has only one row.

See also  Why ChatGPT Is So Important Today
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More