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

COUNT and MAX from unrelated tables

I have two tables which are unrelated to each other. I want to do a count on one column in one table and max of one column in another table.

I have two tables which are unrelated to each other. I want to do a count on one column in one table and max of one column in another table. I have written a query like this:

select a.mycount, b.mymax
from
 (select count(col1) as mycount 
   from Table1 where <cond1>) a, 
 (select max(col2) as mymax 
   from Table2 where <cond2>) b

Is there any better way of doing this?

There are at least two more, but they are only trivially different, and not necessarily better. Yours, by the way, is a perfectly acceptable cross join.

The first method is to use a UNION to produce two summary rows, with an extra column to identify where the aggregate number came from.

select 'Table1'    as source
     , count(col1) as mynumber
  from Table1 where <cond1>
union all
select 'Table2'    as source
     , max(col2)   as mynumber 
  from Table2 where <cond2>

The second is to use subselects in a SELECT from no table.

select (select count(col1)  
          from Table1 where <cond1>) as mycount
     , (select max(col2) as mymax 
          from Table2 where <cond2>) as mymax 

Note that not every database system will allow you to run a SELECT without a FROM clause.

This was last published in February 2006

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close