SQL Having MAX(COUNT) error

Question

Hello guys I am having a problem which is eating me for hours. What I am trying to do is count how many rows have same value from table t2 and show only max values not all of them. But I get error. How I can solve this problem? I can't find specific answer.

Msg 130, Level 15, State 1, Line 5 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

.

SELECT t1.operatoriausPavadinimas,count(t2.operatoriausID) as ct
FROM Operatorius t1,Planas t2
WHERE t1.operatoriausID=t2.operatoriausID 
Group by t1.operatoriausPavadinimas
Having COUNT(t2.operatoriausID)>=MAX(COUNT(t2.operatoriausID))

tag your dbms, show some sample data and the expected result — Vamsi Prabhala

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-12-16T17:58:45

That is correct; nested aggregation functions do not make sense (although nesting aggregation functions inside window functions does make sense). One simple approach uses window functions, which are available in most databases:

SELECT op.*
FROM (SELECT o.operatoriausPavadinimas, count(p.operatoriausID) as ct,
             RANK() OVER (ORDER BY count(p.operatoriausID) DESC) as seqnum
      FROM Operatorius o JOIN
           Planas p
           ON o.operatoriausID = p.operatoriausID 
      GROUP BY o.operatoriausPavadinimas
     ) op
WHERE seqnum = 1;

Notes:

Table aliases that are abbreviations are better than random names like t1 and t2 (or a and b).
Learn to use explicit JOIN syntax. Simple rule: Never use commas in the FROM clause.

EDIT:

Okay, in SQL Server, the easiest is TOP WITH TIES:

      SELECT TOP (1) WITH TIES o.operatoriausPavadinimas, count(p.operatoriausID) as ct
      FROM Operatorius o JOIN
           Planas p
           ON o.operatoriausID = p.operatoriausID 
      GROUP BY o.operatoriausPavadinimas
      ORDER BY COUNT(p.operatoriausID) DESC

SQL Having MAX(COUNT) error

1 Answers