select count(*) from select

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)

1 Answer

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;