In SQL how to count the number of result?

Question

I'm having the following SQL DB:

DB name: films

DB fields: id, title, release_year, country, duration, language, certification, gross, budget

I'm trying to solve the following question: Count in how many different years were more than 200 movies released? 

I have tried to run the following SQL query:

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(release_year) > 200;

But I'm getting the years that have released after 2000. (total there are 13 results) When trying to use SELECT count(release_year) I'm not getting 13 (I'm getting count for each year)

so how can I count the total number of results ?

Answer 1

An elegant way to solve your problem, if your database support analytic functions:

SELECT TOP 1 COUNT(*) OVER() AS num_movies
FROM films
GROUP BY release_year
HAVING COUNT(*) > 200;

The above is for SQL Server, on MySQL 8+ and Postgres use:

SELECT COUNT(*) OVER() AS num_movies
FROM films
GROUP BY release_year
HAVING COUNT(*) > 200
LIMIT 1;