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

Counts from two tables in one SQL statement

I have two tables, both of which have a date field in them. Is there a single SQL statement I can run to get counts...

of rows in both tables for a given date?

Short questions are so yummy.

Three methods come immediately to mind.

select 'new customers' as counttype
     , count(*) as rowcount
  from customers
 where registeredDate = '2003-12-24'
union all
select 'gross sales' as counttype
     , count(*) as rowcount
  from sales
 where salesDate = '2003-12-24'

Don't forget: UNION ALL, not UNION.

select ( 
       select count(*) as newcustomers
         from customers
        where registeredDate = '2003-12-24'
       )
     , (
       select count(*) as grosssales
         from sales
        where salesDate = '2003-12-24'
       )

Sweet, eh? Of course, not every database system allows you to omit the FROM clause.

select c.newcustomers
     , s.grosssales
  from ( 
       select count(*) as newcustomers
         from customers
        where registeredDate = '2003-12-24'
       ) as c
cross
  join (
       select count(*) as grosssales
         from sales
        where salesDate = '2003-12-24'
       ) as s

This works because each subselect returns only one row, so it's okay to cross join them. You could also replace the keywords CROSS JOIN with a comma.


This was last published in August 2004

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