clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335649 fiddles created (27441 in the last week).

CREATE TABLE movie ( id int PRIMARY KEY , yr int ); CREATE TABLE actor ( id int PRIMARY KEY , name text NOT NULL -- assuming name unique? ); CREATE TABLE casting ( movieid int REFERENCES movie , actorid int REFERENCES actor , PRIMARY KEY (movieid, actorid) ); INSERT INTO actor VALUES (1, 'Buster Keaton') , (2, 'John Travolta') , (3, 'Harvey Keitel') ; INSERT INTO movie VALUES ( 1, '1976') , ( 2, '1976') , ( 3, '1977') , ( 4, '1977') , ( 5, '1978') , ( 6, '1978') , ( 7, '1981') , ( 8, '1981') , ( 9, '1994') , (10, '1994') , (11, '1989') , (12, '1999') ; INSERT INTO casting(actorid, movieid) VALUES (1, 1) , (1, 12) , (2, 1) , (2, 2) , (2, 3) , (2, 4) , (2, 5) , (2, 6) , (2, 7) , (2, 8) , (2, 9) , (2, 10) , (2, 11) , (3, 11) ;
3 rows affected
12 rows affected
14 rows affected
 hidden batch(es)


-- Yes, you can do a `max(count(*))` in SQL SELECT m.yr, count(*) AS movie_count , max(count(*)) OVER () AS max_ct FROM casting c JOIN movie m ON c.movieid = m.id WHERE c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta') GROUP BY m.yr ORDER BY count(*) DESC;
yr movie_count max_ct
1976 2 2
1977 2 2
1978 2 2
1981 2 2
1994 2 2
1989 1 2
 hidden batch(es)