-- Oracle 19c: APPROX_PERCENTILE function

SELECT 
  dept_id "Department",
  COUNT(*)  cnts
  APPROX_PERCENTILE(0.25 DETERMINISTIC)
     WITHIN GROUP (ORDER BY sal_mth ASC) "25th Percentile Salary",
  APPROX_PERCENTILE(0.50 DETERMINISTIC)
     WITHIN GROUP (ORDER BY sal_mth ASC) "50th Percentile Salary",
  APPROX_PERCENTILE(0.75 DETERMINISTIC)
     WITHIN GROUP (ORDER BY sal_mth ASC) "75th Percentile Salary"
FROM (
  SELECT 101 dept_id,  66500 sal_mth  FROM DUAL  UNION
  SELECT 101 dept_id,  79375 sal_mth  FROM DUAL  UNION
  SELECT 101 dept_id,  36500 sal_mth  FROM DUAL  UNION
  SELECT 102 dept_id,  86250 sal_mth  FROM DUAL  UNION
  SELECT 103 dept_id,  89950 sal_mth  FROM DUAL  UNION
  SELECT 103 dept_id,  49450 sal_mth  FROM DUAL  UNION
  SELECT 103 dept_id,  92800 sal_mth  FROM DUAL
)
GROUP BY dept_id
ORDER BY dept_id
;




SELECT 
  dept_id "Department",
  COUNT(*)  cnts
  APPROX_PERCENTILE(0.25 DETERMINISTIC, 'ERROR_RATE')
     WITHIN GROUP (ORDER BY sal_mth ASC) error_rate,
  APPROX_PERCENTILE(0.25 DETERMINISTIC, 'CONFIDENCE')
     WITHIN GROUP (ORDER BY sal_mth ASC) CONFIDENCE_,
  APPROX_PERCENTILE(0.25 DETERMINISTIC)
     WITHIN GROUP (ORDER BY sal_mth ASC) "25th Percentile",
  APPROX_PERCENTILE(0.50 DETERMINISTIC)
     WITHIN GROUP (ORDER BY sal_mth ASC) "50th Percentile",
  APPROX_PERCENTILE(0.75 DETERMINISTIC)
     WITHIN GROUP (ORDER BY sal_mth ASC) "75th Percentile"
FROM (
  SELECT 101 dept_id,  66500 sal_mth  FROM DUAL  UNION
  SELECT 101 dept_id,  79375 sal_mth  FROM DUAL  UNION
  SELECT 101 dept_id,  36500 sal_mth  FROM DUAL  UNION
  SELECT 102 dept_id,  86250 sal_mth  FROM DUAL  UNION
  SELECT 103 dept_id,  89950 sal_mth  FROM DUAL  UNION
  SELECT 103 dept_id,  49450 sal_mth  FROM DUAL  UNION
  SELECT 103 dept_id,  92800 sal_mth  FROM DUAL
)
GROUP BY dept_id
ORDER BY dept_id
;




Note: APPROX_PERCENTILE was introduced in Oracle 12c