[Solved] must appear in the GROUP BY clause or be used in an aggregate function

Today, during the grouping statistics, PgSQL reported errors most appear in the group by clause or be used in an aggregate function , which is OK in mysql, but PgSQL reported errors. I went to stackoverflow to query and found that someone had encountered the same problem as me. This is a common aggregation problem in PgSQL. Before the SQL3 standard, PgSQL reported errors, The fields selected for display must appear in group by . Let me describe the problem as follows:

There is a table called makerar , in which the records are as follows: CNAME | wmname | AVG
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

I want to query the maximum AVG of each CNAME , which is written as mysql

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;

Report error in PgSQL

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

This means that the wmname field must appear in group by or be used for aggregate functions

So I added the wmname field after group by according to the error prompt

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;

The result is

cname wmname avg
canada zoro 2.00
spain luffy 1.00
spain usopp 5.00

And what I expect is

cname wmname avg
canada zoro 2.00
spain usopp 5.00

There are two solutions, but I only understand one, so I recorded this one

The general idea is to complete the aggregation in the subquery, and then associate the table containing the fields you want to display (in this case, makerar itself) to get the fields (in this case, wmname), so the SQL will look like this

SELECT   
  t.cname,
  m.wmname,
  t.max
FROM 
  (SELECT 
    cname,
    MAX(avg) AS max 
  FROM makerar
  GROUP BY cname) t
LEFT JOIN makerar m ON t.cname = m.cname AND t.max = m.avg;

https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function#

Similar Posts: