CODEWITHSUNDEEP
sqlcountmax
Kendi Balazs
Kendi Balazs

Reputation: 63

HAVING MAX(COUNT(*)) not working

I have the following code:

SELECT  e.Student_ID, s.Name, s.Surname, Result_IS, COUNT(*) 
FROM Students s 
LEFT JOIN Exams e
  ON e.Student_ID=s.Student_ID
WHERE Result_IS='Negative'
GROUP BY e.Student_ID, s.Name, s.Surname, Result_IS
HAVING COUNT(*)=
(
  SELECT MAX(COUNT(*)) FROM Exams
  WHERE Student_ID=e.Student_ID
    AND Result_IS='Negative'
  GROUP BY e.Student_ID, s.Name, s.Surname, Result_IS
)

I'm having problem with the HAVING COUNT(*) which should select the row where the COUNT(*) gave the biggest result, but instead it just giving me the output of the first select, which is the following:

The output

I've been trying all sort of things but nothing works to select the row, where count is the maximum.

Just give me a hint please in which direction should I move from here, and what is wrong with the code.

Upvotes: 0

Views: 4325

Answers (3)

Wjdavis5
Wjdavis5

Reputation: 4151

Why not just order by count(*) desc and select top 1?

SELECT top 1  e.Student_ID, s.Name, s.Surname, Result_IS, COUNT(*) FROM Students s LEFT JOIN Exams e
ON e.Student_ID=s.Student_ID
WHERE Result_IS='Negative'
GROUP BY e.Student_ID, s.Name, s.Surname, Result_IS
order by count(*) desc

I think this is the syntax for Oracle

  Select * FROM 
    (
            SELECT e.Student_ID, s.Name, s.Surname, Result_IS, COUNT(*) FROM Students s LEFT JOIN Exams e
            ON e.Student_ID=s.Student_ID
            WHERE Result_IS='Negative'
            GROUP BY e.Student_ID, s.Name, s.Surname, Result_IS
            order by count(*) desc
) 
    Where rownum=1

Upvotes: 1

Kendi Balazs
Kendi Balazs

Reputation: 63

So, I found out how to do it, the solution is the following:

SELECT  e.Student_ID, s.Name, s.Surname, Result_IS, COUNT(*) FROM Students s LEFT JOIN Exams e
ON e.Student_ID=s.Student_ID
WHERE Result_IS='Negative'
GROUP BY e.Student_ID, s.Name, s.Surname, Result_IS
HAVING COUNT(*)=
(
SELECT MAX(count) from
(
SELECT count(*) as count FROM Exams
WHERE Result_Is='Negative'
GROUP BY Student_ID
) 
)

Upvotes: 0

swemkg
swemkg

Reputation: 117

Do

HAVING COUNT(*)=
(
SELECT MAX(COUNT(*)) FROM Exams
WHERE Student_ID=e.Student_ID
AND Result_IS='Negative'
GROUP BY e.Student_ID, s.Name, s.Surname, Result_IS
)

query on the joined table. :)

Upvotes: 1

Related Questions