PostgreSQL中的嵌套聚合函数

我想回报每一位author_id，author_name和AVG(total)每author每一个article_group。我想疙瘩author_id，author_name和AVG(total)成阵列。我知道此查询将返回article_group每个数组，但这很好。

我最初尝试将AVG(total)（而不是avg_total）放入我的电脑中array_agg()。这导致出现错误消息，指出我不能嵌套聚合函数。

我一直在尝试找出解决方法，但似乎无法解决。我尝试将子查询放在WHERE子句中AS avg_total，但没有用。

因此，现在我尝试将AS avg_total别名放在FROM子句之前的独立子查询中，但仍然无法使用。

这是查询：

        SELECT b.article_group_id, b.article_group,
        array_agg('[' || c.author_id || ',' || c.author_name || ',' || avg_total || ']'),
        AVG((SELECT total
        FROM article f
        LEFT JOIN article_to_author w ON f.article_id = w.article_id
        LEFT JOIN author v ON w.author_id = c.author_id 
        LEFT JOIN grade z ON f.article_id = z.article_id
        ) AS avg_total)
        
        FROM article f
        LEFT JOIN article_group b ON b.article_group_id = f.article_group_id 
        LEFT JOIN article_to_author w ON f.article_id = w.article_id
        LEFT JOIN author c ON w.author_id = c.author_id 
        GROUP BY b.article_group_id, b. article_group
        

这是当前的错误消息：

    { error: syntax error at or near "AS"
    at Connection.parseE (Z:\GitFolder\roqq\server\node_modules\pg\lib\connection.js:614:13)
    at Connection.parseMessage (Z:\GitFolder\roqq\server\node_modules\pg\lib\connection.js:413:19)
    at Socket.<anonymous> (Z:\GitFolder\roqq\server\node_modules\pg\lib\connection.js:129:22)
    at Socket.emit (events.js:198:13)
    at Socket.EventEmitter.emit (domain.js:448:20)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'error',
  length: 92,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '430',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1149',
  routine: 'scanner_yyerror' }

这是我的桌子：

  CREATE TABLE article(
    article_id          SERIAL PRIMARY KEY,
    article_title       VARCHAR (2100),
    article_group_id    INTEGER 
);

    CREATE TABLE article_to_author(
    ata_id      SERIAL PRIMARY KEY,
    article_id  INTEGER,
    author_id   INTEGER
);

    CREATE TABLE author(
    author_id   SERIAL PRIMARY KEY,
    author_name VARCHAR(500)
);

    CREATE TABLE grade(
    grade_id       SERIAL PRIMARY KEY,
    detail         INTEGER,
    s_g            INTEGER,
    total          INTEGER,
    article_id     INTEGER
);

    CREATE TABLE article_group(
    article_group_id SERIAL PRIMARY KEY,
    article_group    VARCHAR(2100)
);