CODEWITHSUNDEEP
sqlsql-server
Alaa Jabre
Alaa Jabre

Reputation: 1893

Error : aggregate may not appear in the WHERE clause

I'm trying to get the max of column:

select * from
( select col1, count(*) as cnt from talbe1
group by col1
) dt
where cnt = max(cnt)

I tried to get exact value and it worked such as:

where cnt = 5

or 

where cnt > 3

that was OK so what is wrong with the first query?

Edit: the numbers I put there (5, 3) are completely random, I want to get the maximum number of cnt.

Upvotes: 0

Views: 94

Answers (4)

Kaf
Kaf

Reputation: 33839

You can do this with HAVING clause. For example if you want to get cnt=3 records

Select col1, count(*) as cnt from talbe1
Group by col1
Having count(*)=3

If you want to get MAX(cnt)

Select Top(1) col1, count(*) as cnt from talbe1
Group by col1
Order by cnt desc

Upvotes: 2

TechDo
TechDo

Reputation: 18659

How about this query:

select * from
(
    select 
        col1, 
        count(*) as cnt, 
        RANK() OVER(ORDER BY count(*) DESC) AS ran 
    from talbe1 
    group by col1
) dt
where ran=1

Upvotes: 1

Alaa Jabre
Alaa Jabre

Reputation: 1893

I found a solution,
it was pretty simple:(I should have focused more)

select max(cnt) from
( select Fld301, count(*) as cnt from TbC3
group by Fld301
) dt

Upvotes: 1

Richard
Richard

Reputation: 30628

Aggregate clauses have to go in the HAVING section. However, this won't work with your query as is. What you probably wanted to do was:

select top 1 col1, count(*) as cnt 
from talbe1
group by col1
order by count(*) desc

Upvotes: 2

Related Questions