Count unique records in 2 tables

Question

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. For 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)

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.