Puis-je faire un max (count (*)) en SQL?

Voici mon code:

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 

Voici la question

Les années les plus occupées pour John Travolta. Affiche le nombre de films qu’il a réalisés pour chaque année.

Voici la structure de la table:

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

C’est le résultat que j’obtiens:

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

Je dois obtenir les lignes pour lesquelles le count(*) est max.

Comment puis-je faire cela?

Utilisation:

  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 

La commande par num_movies DESC mettra les valeurs les plus élevées en haut du jeu de résultats. Si plusieurs années ont le même compte, m.yr placera l’année la plus récente en haut … jusqu’à ce que la prochaine valeur num_movies change.

Puis-je utiliser un MAX (COUNT (*))?


Non, vous ne pouvez pas superposer les fonctions d’agrégation les unes sur les autres dans la même clause SELECT. L’agrégat interne devrait être effectué dans une sous-requête. C’EST À DIRE:

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

Ordre juste par count(*) desc et vous obtiendrez le plus haut (si vous le combinez avec la 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 

c’est à partir de ce site – http://sqlzoo.net/3.htm 2 solutions possibles:

avec TOP 1 un 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) 

avec 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) 

Utiliser max avec une limite ne vous donnera que la première ligne, mais s’il y a deux lignes ou plus avec le même nombre de films maximum, vous allez manquer certaines données. Voici un moyen de le faire si la fonction rank () est disponible.

 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 

Le code suivant vous donne la réponse. Il implémente essentiellement MAX (COUNT (*)) en utilisant ALL. Il présente l’avantage d’utiliser des commandes et des opérations très basiques.

 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) 

Selon la firebase database que vous utilisez …

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

La plupart de mes expériences concernent Sybase, qui utilise une syntaxe différente de celle des autres bases de données. Mais dans ce cas, vous nommez votre colonne de comptage, vous pouvez donc la sortinger en ordre décroissant. Vous pouvez aller plus loin et limiter vos résultats aux 10 premières lignes (pour trouver ses 10 années les plus occupées).

  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 

Cette question est ancienne, mais a été référencée dans une nouvelle question sur dba.SE. Je pense que les meilleures solutions n’ont pas encore été fournies, alors j’en ajoute une autre.

Tout d’abord, en supposant une intégrité référentielle (généralement imposée avec des contraintes de clé étrangère), vous n’avez pas besoin de vous connecter au movie table. C’est du fret mort dans votre requête. Toutes les réponses à ce jour ne le signalent pas.


Puis-je faire un max(count(*)) en SQL?

Pour répondre à la question dans le titre: Oui , dans Postgres 8.4 (publié le 01/07/2009, avant que cette question ne soit posée) ou plus tard, vous pouvez y parvenir en imbriquant une fonction d’agrégat dans une fonction de fenêtre :

 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; 

Considérez la séquence d’événements dans une requête SELECT :

  • Meilleur moyen d’obtenir le nombre de résultats avant l’application de LIMIT

L’inconvénient (possible): les fonctions de fenêtre n’agrègent pas les lignes. Vous obtenez toutes les lignes après l’étape d’agrégation. Utile dans certaines requêtes, mais pas idéal pour celui-ci.


Pour obtenir une ligne avec le nombre le plus élevé, vous pouvez utiliser ORDER BY ct LIMIT 1 comme @wolph a suggéré :

 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; 

En utilisant uniquement des fonctionnalités SQL de base disponibles dans tous les SGBDR décents à mi-chemin, la mise en œuvre de LIMIT varie:

  • Éléments de sélection SQL où la sum des champs est inférieure à N

Ou vous pouvez obtenir une ligne par groupe avec le nombre le plus élevé avec DISTINCT ON (uniquement Postgres):

  • Sélectionnez la première ligne de chaque groupe GROUP BY?

Répondre

Mais vous avez demandé:

… les lignes pour lesquelles le compte (*) est max.

Peut-être plus d’un. La solution la plus élégante est la fonction de fenêtre rank() dans une sous-requête. Ryan a fourni une requête mais cela peut être plus simple (détails dans ma réponse ci-dessus):

 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; 

Toutes les fonctions majeures de la fenêtre de support du SGBDR sont de nos jours. Sauf MySQL et forks ( MariaDB semble les avoir enfin implémentés dans la version 10.2 ).

 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

Merci à la dernière réponse

 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) 

J’ai eu le même problème: j’avais besoin de connaître uniquement les enregistrements dont leur compte correspondait au nombre maximal (cela pourrait être un ou plusieurs enregistrements).

Je dois en apprendre davantage sur la “clause ALL”, et c’est exactement le type de solution simple que je cherchais.