How to use Count result in the Where clause-CodePudding

I have a SQL query for 'SQL server', which COUNT based on the values of id columns. Like this:

SELECT 
 [id], 
COUNT(*) AS IdCount,   
FROM  myTable  
 where
DATEDIFF(day,[Time],GETDATE()) < 30   GROUP BY [id]

I want to add the value of the COUNT result in the where condition. How can I do that?

SELECT 
[id],
COUNT(*) AS IdCount,
RANK() OVER ( 
   ORDER BY IdCount
) CountRank
  FROM  myTable
  where DATEDIFF(day,[Time],GETDATE()) < 30 
   and  {Count values is > 100}
  GROUP BY [id]

CodePudding user response：

Aggregate functions can't be used in the where clause but below the group by you can add them in a having clause:

select id
from table
group by id
having count(*)>1

CodePudding user response：

SQL does not allow aggregate functions, such as COUNT or SUM, in the where clause. You can use a subquery or the HAVING function. You can see the documentation for a subquery for Microsoft SQL Server (here)[https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16].

With the information available here, I suspect the HAVING clause might solve your problem more easily. Please see the documentation for the HAVING clause (here)[https://learn.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql?view=sql-server-ver16].

SELECT 
 [id], 
COUNT(*) AS IdCount,   
FROM  myTable  
 where
DATEDIFF(day,[Time],GETDATE()) < 30
GROUP BY [id]
HAVING COUNT(IdCount) > SOMENUMBER