Problem solve Get help with specific problems with your technologies, process and projects.

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
  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 last published in March 2005

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.