2

I am trying to get a count about a grouped rows. Here is a description of the table of relevance:

column_name     data_type
-------------   ---------
id              integer
title           character varying
release_year    integer
country         character varying
duration        integer
language        character varying
certification   character varying
gross           bigint
budget          bigint

I am to trying to determine how many different years exist during which more than 200 movies were released.

Here is the query I have tried so far:

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

...which gives 13 rows of 1s.

The question is: how to get a number 13?

4

You GROUP BY release_year but you count release years so you will get 1s, nothing else. If you want to count them, you can enclose in a derived table and do another aggregation:

SELECT
    COUNT(*) AS release_years_with_200_titles
FROM
  (
    SELECT 1
    FROM films
    GROUP BY release_year
    HAVING COUNT(title) > 200
  ) AS dt ;

It can be done without a derived table but it's not very clear how it works:

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

The COUNT(*) OVER () will count the number of rows (after the GROUP BY and HAVING). We then can use SELECT DISTINCT or even better LIMIT 1 to get a single 13 in the result and not thirteen rows with 13.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.