getting message Incorrect syntax near ')' in the last line

Question

I am getting this message in SQL Server 2019:

Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ')'.

I am getting the error in the last line.

What mistake am I making?

Select count(*) from (
   select t1.CUST_ID, count(t1.qty)
   from TRANSACTIONS t1
   where t1.qty>0
   group by t1.CUST_ID
   having count(t1.qty)>10)

Answer 1

You must provide the aliases for your subquery and aggregated column:

Select count(*) from (
           select t1.CUST_ID, count(t1.qty) AS CNT
           from TRANSACTIONS t1
           where t1.qty>0
           group by t1.CUST_ID
           having count(t1.qty)>10
        ) AS T

Test it here

Answer 2

Alternatively (to what Slava said), get rid of aggregate function in select clause altogether as you aren't really interested what it returns. You would need it if main select fetched that value as well, but - it doesn't.

select count(*)
from (select t1.cust_id
      from transactions t1
      where t1.qty > 0
      group by t1.cust_id
      having count(t1.qty) > 10
     ) as x