CODEWITHSUNDEEP
mysqlsql
KyuuQ
KyuuQ

Reputation: 65

How do I make an SQL Query to find the max of an aggregate function like "count"?

This is the query I need in English:

Display the animal id, name, and number of exams of the animal(s) with the most examinations done on them.
Consider there might be ties for first place. In that case all tied animals should be returned.

Here's some relevant SQL:

select an_id, an_name, count(distinct ex_id) as NumberExams
from vt_animals
join vt_exam_headers using (an_id)

How can I do this without using desc and limit and ideally with group by? I thought of using max, but it doesn't seem to work with count.

Upvotes: 1

Views: 104

Answers (4)

user3162968
user3162968

Reputation: 1046

First select animal with most examinations:

SELECT an_id,count(ex_id) FROM animals GROUP BY an_id ORDER BY count(*) DESC LIMIT 1

Then you can use it as a subquery.

Explanation: you sort this table descending by count(*) and then you choose top 1, which is maximum.

Upvotes: 1

ystan-
ystan-

Reputation: 1536

Depending on the database product you're using, this could vary in complexity. For example, emibloque's answer will not work in MS SQL Server because the having clause needs to correspond with the group by clause. In this case, you'd have to do something along these lines:

select * from
(
    select an_name, count(*) exams
    from vt_animals a join vt_exam_headers e on a.an_id = e.an_id
    group by an_name
) sub1
where exams =
(
    select max(exams) from
    (
        select an_id, count(*) exams
        from vt_exam_headers
        group by an_id
    ) sub2
)

or if you prefer the use of variables:

declare @max_exams int;
select @max_exams = (
    select max(exams) from
    (
        select an_id, count(*) exams
        from vt_exam_headers
        group by an_id
    ) sub 
);
select * from
(
    select an_name, count(*) exams
    from vt_animals a join vt_exam_headers e on a.an_id = e.an_id
    group by an_name
) sub1
where exams = @max_exams

Upvotes: 0

emibloque
emibloque

Reputation: 203

If I understand well the query, something like this would return the group of animals if more than one have the most number of examinations:

SELECT a.an_id, a.an_name, a.number_exams
FROM (SELECT an_id, an_name, COUNT(ex_id) as number_exams
      FROM vt_animals
      JOIN vt_exam_headers USING (an_id)
      GROUP BY an_id) AS a
HAVING a.number_exams >= MAX(a.number_exams)

Upvotes: 2

G one
G one

Reputation: 2729

You have to use group by clause to the column names which are not in the aggregate functions

select an_id, an_name, count(distinct ex_id) as NumberExams
from vt_animals
group by an_id, an_name

Upvotes: 1

Related Questions