How do I limit my query results to results that include a certain number of items in a column?-CodePudding

I am trying to query results from my movie database that returns movies that have at least 3 actors listed. I just need to get the titleid and I can go from there. How do I pull the titleid from the table actors where the number of personids > 3.

I have tried:

SELECT titleid FROM actors WHERE COUNT(personid) > 3;

But this appears to be a misuse of COUNT(). Is there a function I can use?

SCHEMA:

CREATE TABLE actors(
  titleid TEXT,
  ordering INT,
  personid TEXT,
  category TEXT,
  job TEXT,
  characters TEXT
);

CREATE TABLE actornames(
  personid TEXT,
  name TEXT,
  birth INT,
  death INT,
  profession TEXT,
  knowntitles TEXT,
  titleid TEXT,
  ordering INT,
  "personid:1" TEXT,
  category TEXT,
  job TEXT,
  characters TEXT
);

CREATE TABLE ratings (titleid TEXT, rating DEC, votes INT);

CREATE TABLE movielist(title TEXT,titleid TEXT);```

CodePudding user response：

You can use COUNT() aggregate function in the HAVING clause of an aggregation query:

SELECT titleid
FROM actors
GROUP BY titleid
HAVING COUNT(*) >= 3;

Just in case an actor may be listed more than once in actors for the same movie, you should count the distinct personids:

SELECT titleid
FROM actors
GROUP BY titleid
HAVING COUNT(DISTINCT personid) >= 3;