How to COUNT (*) WHERE column1 is >= The maximum value for specific value in column2?

user_19886181 0
Viewed 57
title duration
programme a 1000
programme b 1500

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

sql
3 Answers

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.

user_34693910

This might work:

SELECT COUNT(*) FROM your_table, (SELECT MAX(duration) AS m FROM your_table) AS sq 
WHERE duration/m >= 0.9
user_140151260 
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
user_145026880
Related