adding the sum of two select count queries

Question

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

SELECT t1.numRows + t2.numRows FROM tblOne t1 CROSS JOIN tblTwo t2 — Lamak
– Lamak, Commented Oct 28, 2015 at 14:45

D Stanley · Accepted Answer · 2015-10-28 14:46:50Z

6

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

answered Oct 28, 2015 at 14:46

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lamak Over a year ago

May I ask why an implicit CROSS JOIN instead of an explicit one?

D Stanley Over a year ago

@Lamak No strong reason - just a shorter syntax, and no concern about cartesian product issues since each subquery is only one record. I prefer the subqueries to be honest.

Gordon Linoff · Accepted Answer · 2015-10-28 14:46:10Z

2

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;

answered Oct 28, 2015 at 14:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Janne Matikainen · Accepted Answer · 2015-10-29 07:55:33Z

2

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.

edited Oct 29, 2015 at 7:55

answered Oct 28, 2015 at 14:48

Janne Matikainen

5,13118 silver badges22 bronze badges

Collectives™ on Stack Overflow

adding the sum of two select count queries

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related