Nested aggregate function in PostgreSQL

Question

I am trying to return every author_id, author_name, and AVG(total) per author for every article_group. I am trying to lump the author_id, author_name, and AVG(total) into arrays. I understand that this query will return an article_group per array, but that is fine.

I originally tried putting AVG(total) (instead of avg_total) inside my array_agg(). This resulted in an error message stating that I cannot have nested aggregate functions.

I have been trying to figure out a work around and can't seem to figure one out. I tried putting a subquery in WHERE clause AS avg_total and that didn't work.

So now I tried putting the AS avg_total alias in an independent subquery before the FROM clause and it is still not working.

Here is the query:

        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

And this is the current error message:

    { 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' }

Here are my tables:

  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)
);

Please add some sample data and sample output also. This will help us to help you — Akhilesh Mishra, Aug 10 '20 at 05:08
I have tried your original comment. I get: error: column "avg_total" does not exist. All I am getting returned are errors. And there is no specific form of data that goes in. It merely returns all the data in organized arrays per article_group — SC4RECROW, Aug 10 '20 at 05:10
actually that i have commented for syntax error. if you post some data and sample output then. we can post correct answer. — Akhilesh Mishra, Aug 10 '20 at 05:12
Forgive me but I do not understand what you mean by 'sample output'. All my I am getting for outputs are errors. Do you want me to write the format for which I expect the output to be? — SC4RECROW, Aug 10 '20 at 05:15

score 3 · Accepted Answer · edited Aug 10 '20 at 12:46

In your question so many things are unclear. Based on what i understood from your current query, try this:

with cte as (
  SELECT ag.article_group_id,
         ag.article_group,
         au.author_id, 
         au.author_name, 
         avg(gr.total) as avg_total
    FROM article_group ag
         LEFT JOIN article ar on ar.article_group_id=ag.article_group_id
         LEFT JOIN article_to_author ata ON ar.article_id = ata.article_id
         LEFT JOIN author au ON ata.author_id = au.author_id 
         LEFT JOIN grade gr ON ar.article_id = gr.article_id
   GROUP BY ag.article_group_id, ag.article_group, au.author_id, au.author_name
)
SELECT article_group_id, 
       article_group,
       array_agg('[' || author_id || ',' || author_name || ',' || avg_total || ']')
  FROM cte 
 GROUP BY article_group_id, article_group

You can change whatever you want in array_agg

DEMO

score 0 · Answer 2 · answered Aug 10 '20 at 14:44

I think this is two levels of aggregation. This allows you to calculate the overall average accurately:

SELECT article_group_id, array_agg( (author_id, author_name, avg_grade)) as authors,
       SUM(total_grade) / SUM(num_grade) as group_avg
FROM (SELECT ag.article_group_id, au.author_id, au.author_name,
             AVG(gr.total) as avg_grade,
             SUM(gr.total) as total_grade,
             COUNT(gr.total) as num_grade
      FROM article_group ag LEFT JOIN
           article ar
           ON ar.article_group_id=ag.article_group_id LEFT JOIN
           article_to_author ata
           ON ar.article_id = ata.article_id LEFT JOIN
           author au
           ON ata.author_id = au.author_id LEFT JOIN
           grade gr
           ON ar.article_id = gr.article_id
      GROUP BY ag.article_group_id, au.author_id, au.author_name
     ) a
GROUP BY article_group_id;

Notice that this aggregates the authors as an array rather than a string. Of course, you can use a string if you prefer, but arrays are usually cleaner and more versatile.

Nested aggregate function in PostgreSQL

2 Answers2