Q

Sum of two table row counts as one answer

I have two tables, each of them have following fields: ID, PartNumber,.. I simply want to count the rows from both tables in one SQL expression. For example, if there are 20 rows in Table1 and 21 rows in Table2, what I want as a result is Sum:41!

There are several ways to approach this. Here is the most non-convoluted:

select ( select count(*) from Table1 )
     + ( select count(*) from Table2 ) 
          as total_rows

This query uses two scalar subqueries, and adds the two scalar values tegether for the answer. This query is also missing the FROM clause, and while my knowledge of the SQL standard is sketchy, I know this is invalid, because I've looked this up once before, in Counts from two tables in one SQL statement (13 August 2004). Whenever I have doubt about whether some query is valid SQL, I always use the Mimer SQL Validator, and even in SQL-2003, you must apparently have a FROM clause. Nevertheless, the query does work in both MySQL and Microsoft SQL Server!

If you happen to have a one-row table lying around, such as Oracle's DUAL, you can simply throw that into the query. Note that you don't actually have to select anything from it! If you're not using Oracle, you should create your own one-row table, as it will have other handy uses (which are not relevant here).

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

You could also add up the two separate counts in a UNION query:

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.

Finally, you 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. As the previous article noted, you could also obtain a cross join by replacing the words CROSS JOIN with a comma, but that's the "old school" syntax.


This was first published in March 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close