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.