Count unique records in 2 tables

I have two tables that contain data from different temperature sensors:

Table1

_TimeStamp Temperature

2009-12-20 11:59:56.2 10.1

2009-12-20 11:59:56.3 10.2

2009-12-20 11:59:56.4 11.0

2009-12-20 11:59:56.5 Null

Table2

_TimeStamp Temperature

2009-12-20 11:59:56.2 10.5

2009-12-20 11:59:56.5 9.8

2009-12-20 11:59:56.7 12.0

2009-12-20 11:59:56.9 10.0

I want to count the number of records for which either one of the two temperature sensors made a non-null measurement. F开发者_开发百科or the example above, the count is 6, though there are actually 864000 or so records in each table.

I know the following SQL Server query is wrong, but could you help correct it?

(SELECT DISTINCT COUNT(_TimeStamp) FROM Table1) UNION  (SELECT DISTINCT COUNT(_TimeStamp) FROM Table2)

Have a look at this

DECLARE @Table1 TABLE(
        _TimeStamp DATETIME,
        Temperature FLOAT
)

DECLARE @Table2 TABLE(
        _TimeStamp DATETIME,
        Temperature FLOAT
)

INSERT INTO @Table1 SELECT '2009-12-20 11:59:56.2',10.1  
INSERT INTO @Table1 SELECT '2009-12-20 11:59:56.3',10.2  
INSERT INTO @Table1 SELECT '2009-12-20 11:59:56.4',11.0  
INSERT INTO @Table1 SELECT '2009-12-20 11:59:56.5',Null  


INSERT INTO @Table2 SELECT '2009-12-20 11:59:56.2',10.5  
INSERT INTO @Table2 SELECT '2009-12-20 11:59:56.5',9.8  
INSERT INTO @Table2 SELECT '2009-12-20 11:59:56.7',12.0  
INSERT INTO @Table2 SELECT '2009-12-20 11:59:56.9',10.0  

SELECT COUNT(1) TOTAL
FROM    (
            SELECT _TimeStamp FROM @Table1 WHERE Temperature IS NOT NULL
            UNION
            SELECT _TimeStamp FROM @Table2 WHERE Temperature IS NOT NULL
        ) sub

By using a UNION and not a UNION ALL, you will get the DISTINCT time stamps.

you need to union first, then get the count.

select count(ts) from
(
 select _timestamp as ts
 from table1
 where temperature is not null
 union  
 select _timestamp 
 from table2
 where temperature is not null
)innerSql

where does the count of 6 come from your data?
also what is wrong with your query?
seems fine to me. you just have to remove the () around selects and name the columns.

You should be able to try something like this.

SELECT
(
   (SELECT DISTINCT COUNT(Id) FROM [Table]) +
   (SELECT DISTINCT COUNT(Id) FROM [Table2])
)

This will give you the sum of the counts from both tables. You can change the subqueries to exhibit whatever conditions you want.