在子查询中，您正在查看一个

WHERE S1.ARTIST_ID = A.ARTIST_ID AND S1.NAME_ALB = S.NAME_ALB

这你得到的

SUM(S1.N_REPRODUCTIONS)

这是一个价值，尽管

GROUP BY S1.ARTIST_ID, S1.NAME_ALB)

因为你看的只是一张专辑。
Oracle允许直接使用另一个聚合，而不必编写子查询。您可以使用此聚合来获得最大总和，但由于只有一个总和，因此您只能获得专辑的复制次数。

HAVING <album's reproduction count> = <album's reproduction count>

最简单的方法是通过MAX OVER得到最大复制总和，然后过滤掉那些行，只保留那些具有最大复制总和的专辑。

SELECT a.name, r.name_alb
FROM
(
  SELECT
    artist_id, name_alb,
    SUM(n_reproductions) AS sum_repros,
    MAX(SUM(n_reproductions)) OVER (PARTITION BY artist_id) AS max_sum_repros
  FROM song
  GROUP BY artist_id, name_alb
) r
JOIN artist a ON a.artist_id = r.artist_id
WHERE r.sum_repros = r.max_sum_repros
ORDER BY a.name, r.name_alb;

演示：https://dbfiddle.uk/Uv6OPBqB

在Oracle 12中，您可以使用：

SELECT MAX(a.name) AS name,
       s.name_album
FROM   artist a
       INNER JOIN song s
       ON (a.artist_id = s.artist_id)
GROUP BY a.artist_id, s.name_album
ORDER BY
       DENSE_RANK() OVER (
         PARTITION BY a.artist_id
         ORDER BY SUM(n_reproductions) DESC
       )
FETCH FIRST ROW WITH TIES;

其中，对于示例数据：

CREATE TABLE Artist (Artist_Id, Name) AS
SELECT 1, 'Ignacio Guitar' FROM DUAL UNION ALL
SELECT 2, 'Rosalia' FROM DUAL UNION ALL
SELECT 3, 'Makande' FROM DUAL;

CREATE TABLE Song (Artist_Id, Name, N_reproductions, Name_album) AS
SELECT 1, 'Song1',  10, 'Album1' FROM DUAL UNION ALL
SELECT 1, 'Song2',  15, 'Album1' FROM DUAL UNION ALL
SELECT 1, 'Song3',  13, 'Album1' FROM DUAL UNION ALL
SELECT 1, 'Song4',  20, 'Album2' FROM DUAL UNION ALL
SELECT 1, 'Song5',  12, 'Album2' FROM DUAL UNION ALL
SELECT 1, 'Song6',  25, 'Album2' FROM DUAL UNION ALL
SELECT 2, 'Song7',  17, 'Album3' FROM DUAL UNION ALL
SELECT 2, 'Song8',  21, 'Album3' FROM DUAL UNION ALL
SELECT 2, 'Song9',  20, 'Album4' FROM DUAL UNION ALL
SELECT 2, 'Song10', 25, 'Album4' FROM DUAL UNION ALL
SELECT 2, 'Song11', 31, 'Album4' FROM DUAL

输出：
| 名称|名称_专辑|
| - -|- -|
| 伊格纳西奥吉他|专辑2|
| 罗萨利亚|专辑4|
fiddle