CODEWITHSUNDEEP
sqlsql-servert-sql
nav100
nav100

Reputation: 3133

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?

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)

Upvotes: 46

Views: 174878

Answers (2)

cottontail
cottontail

Reputation: 23071

Instead of selecting twice, we can also count distinct values in one SELECT. Since we need multiple-column distinct rows, we can concatenate them beforehand via CONCAT.

SELECT COUNT(DISTINCT CONCAT(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

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

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;

Upvotes: 76

Related Questions