Column must appear in the group by clause or be used in an aggregate function

Question

Problem :

I want to select a maximum avg for each cname in my table.

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

But I will get the below error,

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;

How can I go about fixing above error?

Note: This table is the VIEW created from the previous operation.

Answer 1

This is the common aggregation problem. Before the SQL3 (1999),a selected fields must appear in a GROUP BY clause[*].

To workaround your issue, you must calculate a aggregate in the sub-query and then join it with itself to get a additional columns you'd need to show as below:

SELECT m.cname, m.wmname, t.mx

FROM (

    SELECT cname, MAX(avg) AS mx

    FROM makerar

    GROUP BY cname

    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg;

 cname  | wmname |          mx          

--------+--------+------------------------

 canada | zoro   |     2.0000000000000000

 spain  | usopp  |     5.0000000000000000

But you may also use the window functions, which looks simpler as shown below :

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx

FROM makerar;

The only issue with this method is that it will show all the records (window functions do not group). But it will show a correct (i.e. maxed at cname level) MAX for a country in each row, so it is up to you.