SQL에서 max (count (*))를 할 수 있습니까?

SQL에서 max (count (*))를 할 수 있습니까?

---

내 코드는 다음과 같습니다.

    select yr,count(*)  from movie
join casting on casting.movieid=movie.id
join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr

여기에 질문이 있습니다

'존 트라볼타'가 가장 바쁜 해였습니다. 그가 매년 만든 영화의 수를 보여줍니다.

다음은 테이블 구조입니다.

movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)

이것은 내가 얻는 출력입니다.

yr  count(*)
1976    1
1977    1
1978    1
1981    1
1994    1
etcetc

count(*)최대 행을 가져와야합니다.

어떻게해야합니까?

---

사용하다:

  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

정렬 기준 num_movies DESC은 결과 집합의 맨 위에 가장 높은 값을 배치합니다. 여러 해의 개수가 같으면 m.yr다음 num_movies값이 변경 될 때까지 가장 최근 연도가 맨 위에 표시됩니다.

MAX (COUNT (*)) 사용할 수 있습니까?

---

아니요, 동일한 SELECT 절에서 서로 위에 집계 함수를 계층화 할 수 없습니다. 내부 집계는 하위 쿼리에서 수행되어야합니다. IE :

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

---

주문 만하면 count(*) desc최고를 얻을 수 있습니다 (와 결합하면 limit 1).

---

SELECT * from 
(
SELECT yr as YEAR, COUNT(title) as TCOUNT
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr
order by TCOUNT desc
) res
where rownum < 2

---

이 사이트 -http: //sqlzoo.net/3.htm 2 가지 가능한 해결책 :

TOP 1 a ORDER BY ... DESC :

SELECT yr, COUNT(title) 
FROM actor 
JOIN casting ON actor.id=actorid
JOIN movie ON movie.id=movieid
WHERE name = 'John Travolta'
GROUP BY yr
HAVING count(title)=(SELECT TOP 1 COUNT(title) 
FROM casting 
JOIN movie ON movieid=movie.id 
JOIN actor ON actor.id=actorid
WHERE name='John Travolta'
GROUP BY yr
ORDER BY count(title) desc)

MAX :

SELECT yr, COUNT(title) 
FROM actor  
JOIN casting ON actor.id=actorid    
JOIN movie ON movie.id=movieid
WHERE name = 'John Travolta'
GROUP BY yr
HAVING 
    count(title)=
        (SELECT MAX(A.CNT) 
            FROM (SELECT COUNT(title) AS CNT FROM actor 
                JOIN casting ON actor.id=actorid
                JOIN movie ON movie.id=movieid
                    WHERE name = 'John Travolta'
                    GROUP BY (yr)) AS A)

---

제한이있는 max를 사용하면 첫 번째 행만 제공되지만 최대 영화 수가 동일한 행이 두 개 이상있는 경우 일부 데이터를 놓칠 수 있습니다. 다음은 rank () 함수를 사용할 수 있는 경우이를 수행하는 방법 입니다.

SELECT
    total_final.yr,
    total_final.num_movies
    FROM
    ( SELECT 
        total.yr, 
        total.num_movies, 
        RANK() OVER (ORDER BY num_movies desc) rnk
        FROM (
               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
               WHERE a.name = 'John Travolta'
               GROUP BY m.yr
             ) AS total
    ) AS total_final 
   WHERE rnk = 1

---

다음 코드는 답을 제공합니다. 기본적으로 ALL을 사용하여 MAX (COUNT (*))를 구현합니다. 매우 기본적인 명령과 작업을 사용한다는 장점이 있습니다.

SELECT yr, COUNT(title)
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr HAVING COUNT(title) >= ALL
  (SELECT COUNT(title)
   FROM actor
   JOIN casting ON actor.id = casting.actorid
   JOIN movie ON casting.movieid = movie.id
   WHERE name = 'John Travolta'
   GROUP BY yr)

---

^{이 질문은 오래되었지만 dba.SE의 새로운 질문에서 참조되었습니다 . 아직 최고의 솔루션이 제공되지 않은 것 같아서 다른 솔루션을 추가하고 있습니다.}

First off, assuming referential integrity (typically enforced with foreign key constraints) you do not need to join to the table movie at all. That's dead freight in your query. All answers so far fail to point that out.

---

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

To answer the question in the title: Yes, in Postgres 8.4 (released 2009-07-01, before this question was asked) or later you can achieve that by nesting an aggregate function in a window function:

SELECT c.yr, count(*) AS ct, max(count(*)) OVER () AS max_ct
FROM   actor   a
JOIN   casting c ON c.actorid = a.id
WHERE  a.name = 'John Travolta'
GROUP  BY c.yr;

Consider the sequence of events in a SELECT query:

• Best way to get result count before LIMIT was applied

The (possible) downside: window functions do not aggregate rows. You get all rows left after the aggregate step. Useful in some queries, but not ideal for this one.

---

To get one row with the highest count, you can use ORDER BY ct LIMIT 1 like @wolph hinted:

SELECT c.yr, count(*) AS ct
FROM   actor   a
JOIN   casting c ON c.actorid = a.id
WHERE  a.name = 'John Travolta'
GROUP  BY c.yr
ORDER  BY ct DESC
LIMIT  1;

Using only basic SQL features available in any halfway decent RDBMS - the LIMIT implementation varies:

• SQL select elements where sum of field is less than N

Or you can get one row per group with the highest count with DISTINCT ON (only Postgres):

• Select first row in each GROUP BY group?

---

Answer

But you asked for:

... rows for which count(*) is max.

Possibly more than one. The most elegant solution is with the window function rank() in a subquery. Ryan provided a query but it can be simpler (details in my answer above):

SELECT yr, ct
FROM  (
   SELECT c.yr, count(*) AS ct, rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   actor   a
   JOIN   casting c ON c.actorid = a.id
   WHERE  a.name = 'John Travolta'
   GROUP  BY c.yr
   ) sub
WHERE  rnk = 1;

All major RDBMS support window functions nowadays. Except MySQL and forks (MariaDB seems to have implemented them at last in version 10.2).

---

Depending on which database you're using...

select yr, count(*) num from ...
order by num desc

Most of my experience is in Sybase, which uses some different syntax than other DBs. But in this case, you're naming your count column, so you can sort it, descending order. You can go a step further, and restrict your results to the first 10 rows (to find his 10 busiest years).

---

Thanks to the last answer

SELECT yr, COUNT(title)
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr HAVING COUNT(title) >= ALL
  (SELECT COUNT(title)
   FROM actor
   JOIN casting ON actor.id = casting.actorid
   JOIN movie ON casting.movieid = movie.id
   WHERE name = 'John Travolta'
   GROUP BY yr)

I had the same problem: I needed to know just the records which their count match the maximus count (it could be one or several records).

I have to learn more about "ALL clause", and this is exactly the kind of simple solution that I was looking for.

---

     select top 1 yr,count(*)  from movie
join casting on casting.movieid=movie.id
join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr order by 2 desc

---

create view sal as
select yr,count(*) as ct from
(select title,yr from movie m, actor a, casting c
where a.name='JOHN'
and a.id=c.actorid
and c.movieid=m.id)group by yr

-----VIEW CREATED-----

select yr from sal
where ct =(select max(ct) from sal)

YR 2013

참고URL : https://stackoverflow.com/questions/2436820/can-i-do-a-maxcount-in-sql