How to Get Max(Count) in SQL

Can I do a max(count(*)) in SQL?

Use:

  SELECT m.yr, 
         COUNT(*) AS num_movies
    FROM MOVIE m
    JOIN CASTING c ON c.movieid = m.id
    JOIN ACTOR a ON a.id = c.actorid
                AND a.name = 'John Travolta'
GROUP BY m.yr
ORDER BY num_movies DESC, m.yr DESC

Ordering by num_movies DESC will put the highest values at the top of the resultset. If numerous years have the same count, the m.yr will place the most recent year at the top... until the next num_movies value changes.

Can I use a MAX(COUNT(*)) ?

---

No, you can not layer aggregate functions on top of one another in the same SELECT clause. The inner aggregate would have to be performed in a subquery. IE:

SELECT MAX(y.num)
  FROM (SELECT COUNT(*) AS num
          FROM TABLE x) y

SQL select MAX(COUNT)

I'd try with a ORDER BY max DESC LIMIT 1, where maximum is the count(*) field. Something like:

SELECT "name", count(*) maximum FROM "users" 
   INNER JOIN "microposts" ON "microposts"."user_id" = "users"."id" 
GROUP BY users.id 
ORDER BY maximum DESC 
LIMIT 1

I dont' have mysql available now, so I'm doing this on the paper (and it might not work), but it's just an orientation.

select max count in SQL

Think aggregation and limit:

select (months * salary) as Salary, count(*) as count
from employees
group by months * salary
order by months * salary desc
limit 1;

Get the max count of rows per hours

Use HAVING

SELECT COUNT(*), EXTRACT(HOUR FROM EXPIRY_DATE) eh, EXTRACT(DAY FROM EXPIRY_DATE) ed
FROM TABLE1 
GROUP BY EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
HAVING COUNT(*) >= ALL
(
    SELECT COUNT(*)
    FROM TABLE1 t2
    WHERE EXTRACT(DAY FROM t2.EXPIRY_DATE) = EXTRACT(DAY FROM table1.EXPIRY_DATE)
    GROUP BY EXTRACT(HOUR FROM t2.EXPIRY_DATE), 
)

Get max count of each group

One method uses having:

SELECT payroll, reason, count(reason) as count
FROM table1 as t1
GROUP BY reason, payroll
HAVING t1.reason = (SELECT TOP 1 tt1.reason
                    FROM table1 as tt1
                    WHERE tt1.payroll = t1.payroll
                    GROUP BY tt1.reason
                    ORDER BY COUNT(*) DESC, tt1.reason
                   );

How to get MAX(COUNT) in SQL?

If you want one row, the logic would be group by to calculate the count. Then order by and some way of limiting the result to the top:

SELECT STORE_TYPE, COUNT(*) AS cnt
FROM TRANSACTION_INFO
GROUP BY STORE_TYPE
ORDER BY cnt DESC
FETCH FIRST 1 ROW ONLY;

In the event of ties, this only returns one arbitrary row with the highest value. The best way to handle ties depends on the database.

FETCH FIRST 1 ROW ONLY is the ISO/ANSI standard for limiting the result set to one row. Your database may use a different syntax for this.

I also strongly discourage you from using single quotes for column aliases. Use names that don't need to be escaped -- that is the best advise. Otherwise, use the escape mechanism for your database, typically double quotes, backticks, or square braces.

EDIT:

The solution in SQL Server that includes ties is:

SELECT TOP (1) STORE_TYPE, COUNT(*) AS cnt
FROM TRANSACTION_INFO
GROUP BY STORE_TYPE
ORDER BY cnt DESC;

If you only want one row, remove the WITH TIES.

How do I perform MAX(COUNT(*)) query?

How about the following:

SELECT TOP (1) ItemId,  COUNT(ItemId) AS MCOUNT
FROM            Menu_Item
GROUP BY ItemId
ORDER BY  COUNT(ItemId) DESC

Finding max count of product

In Postgres 13+, you can use FETCH WITH TIES:

select count(*) as count, b.name AS brand_name
from product p join
     brand b 
     on b.Id = p.BrandId
group by b.name
order by count(*) desc
fetch first 1 row with ties;

In older versions you can use window functions.

---

Related Topics