Count from inner select statement

Question

I am trying to execute the following query in SQL server but I am getting error. Can somebody explain me how to count the inner select statement?

SELECT COUNT(*) from

(Select ID,
      COUNT(ID)
  FROM [Testing].[dbo].[Bench] group by ID  having COUNT(*) =5);

Solution

I suppose you just missed the name of the second column:

SELECT COUNT(*)
FROM (
    Select id, COUNT(id) count_of_id
    FROM [Testing].[dbo].[Bench]
    GROUP BY id
    HAVING COUNT(*) =5
);

Comments

[Huzaifa]

AAaaahhhh! Silly me. You are right. Thank you very much.

[Taryn]

SQL Server requires an alias on the subquery.