select count(*) from select

Question

I am trying to get row count from the following query. I get only row count as 1 but there are 35 records. Could you please let me know how to get the count from inner query?

Thank you

SELECT COUNT(*)(SELECT DISTINCT a.my_id, a.last_name, a.first_name, b.temp_val                 FROM Table_A a INNER JOIN Table_B b on a.a_id = b.a_id) 

Answer 1

You're missing a FROM and you need to give the subquery an alias.

SELECT COUNT(*) FROM  (   SELECT DISTINCT a.my_id, a.last_name, a.first_name, b.temp_val    FROM dbo.Table_A AS a     INNER JOIN dbo.Table_B AS b     ON a.a_id = b.a_id ) AS subquery;