CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

adding the sum of two select count queries

I'm using SQL Server 2012.

I have two tables and I'm trying to see how many rows both tables contain. They have no common field on which they can join. Below is my query so far, which obviously doesn't work. How should it be?

;with tblOne as
(
    select count(*) numRows from tblOne where DateEx = '2015-10-27'
),
tblTwo as
(
    select count(*) numRows from tblTwo where Status = 0
)
select tblOne.numRows + tblTwo.numRows

Upvotes: 0

Views: 75

Answers (3)

Janne Matikainen
Janne Matikainen

Reputation: 5121

Use union and query from that

select sum(numRows) as numRows from (
    select count(*) numRows from tblOne where DateEx = '2015-10-27'
    union all
    select count(*) numRows from tblTwo where Status = 0
)

edit: Added union all, so if it happens that both queries have same amount of rows the both results are included.

Upvotes: 2

D Stanley
D Stanley

Reputation: 152521

You don't need CTEs; you can just change them to subqueries:

select 
    (select count(*) numRows from tblOne where DateEx = '2015-10-27') +   
    (select count(*) numRows from tblTwo where Status = 0)

If you REALLY want to use CTEs then just do an implicit CROSS JOIN:

with tblOne as
(
    select count(*) numRows from tblOne where DateEx = '2015-10-27'
),
tblTwo as
(
    select count(*) numRows from tblTwo where Status = 0
)
select tblOne.numRows + tblTwo.numRows
FROM tblOne, tblTwo

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use a cross join:

with tblOne as (
      select count(*) as numRows from tblOne where DateEx = '2015-10-27'
     ),
     tblTwo as (
      select count(*) as numRows from tblTwo where Status = 0
     )
select tblOne.numRows + tblTwo.numRows
from tblOne cross join tblTwo;

Upvotes: 2

Related Questions