• Register
0 votes
421 views

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.

3.9k points

Please log in or register to answer this question.

1 Answer

0 votes

Solution :

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.

38.6k points

Related questions

0 votes
1 answer 28 views
28 views
Problem: Hi I need the solution of this problem please;; column must appear in the group by clause
asked Mar 20 PkGuy 25.9k points
0 votes
1 answer 17 views
17 views
Problem: Why I got an error > Write an aggregate expression for the number of entries in the vendor_name column:
asked Apr 26 ummeshani 10.8k points
0 votes
1 answer 25 views
25 views
Problem: I want to group by sender_id, but I get the following error. Someone help.
asked Apr 28 salim1 60.6k points
0 votes
1 answer 11 views
11 views
Problem: Group by clause and contains nonaggregated column.
asked Jun 29 akhi 125k points
0 votes
1 answer 15 views
15 views
Problem: Please,I want an answer : Which of the following could not be done using an aggregate function?
asked Jun 18 Chi Omega 168k points
0 votes
1 answer 80 views
80 views
Problem : Currently I am using the MySQL 5.7.13 on the windows PC with the WAMP Server While executing below query I am facing following error SELECT * FROM `tbl_customer_pod_uploads` WHERE `load_id` = '78' AND `status` = ' ... column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
asked Feb 18, 2020 mphil 2.3k points
0 votes
1 answer 56 views
56 views
Problem : I am currently using MySQL 5.7.13 on my windows PC with the WAMP Server I am facing following error while executing below the query. Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ' ... functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Does anybody have any solution for above error?
asked Nov 29, 2019 alecxe 7.5k points
0 votes
1 answer 19 views
19 views
Problem: I got an error - Column 'Employee.EmpID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. select loc.LocationID, emp.EmpID from Employee as emp full join Location as loc on emp.LocationID = ... don't want to count nulls from Employee as emp full join Location as loc on emp.LocationID = loc.LocationID group by loc.LocationID
asked Apr 27 Jack20 5.1k points
1 vote
1 answer 30 views
30 views
Problem; I get the following error: Error: aggregate value used where an integer was expected.
asked Apr 29 PkGuy 25.9k points