Counts from two tables in one SQL statement

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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in August 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top