I have a table like this, and I want to count all rows where the duration is at least 90% of the MAXIMUM duration of the programme in said row. How can I do this?

For context, each row is a play event, and duration is how long in seconds the play event lasted

CodePudding user response：

None of the other answers take into account, that the MAX should be calculated per title as per the at least 90% of the MAXIMUM duration of the programme in said row part of the original post.

SELECT
  COUNT(*)
FROM
  table T
  INNER JOIN (
    SELECT title, MAX(duration) AS max_duration FROM table GROUP BY title)
  ) X
    ON T.title= X.title
WHERE
  T.duration >= X.max_duration * 0.9

I am assuming, that the title is unique (per programme) in the above, if not, replace that column with the correct identifier.

CodePudding user response：

This might work:

SELECT COUNT(*) FROM your_table, (SELECT MAX(duration) AS m FROM your_table) AS sq 
WHERE duration/m >= 0.9

CodePudding user response：

WITH max_duration_by_title AS (SELECT title, MAX(duration) AS max_duration FROM your_table GROUP BY title)
SELECT base_table.title
, COUNT(1) AS total_plays
, COUNT (CASE WHEN base_table.duration >= 0.9 * max_duration.max_duration THEN 1 END) AS total_plays_longer_than_threshold
FROM your_table base_table
INNER JOIN max_duration_by_title
   ON base_table.title = max_duration_by_title.title
GROUP BY base_table.title