• Register
0 votes
67 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.

7 5 2
3,870 points

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.

9 7 4
38,600 points

Related questions

0 votes
1 answer 26 views
26 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 mphil 2.3k points
0 votes
1 answer 34 views
34 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
–1 vote
1 answer 55 views
55 views
Problem : I want to display the column B in my below mentioned SQL query, but when I try to add it to my query it gives me following error: Column T2.M' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. My code: SELECT L, COUNT(M) as T1, M FROM T2 WHERE ID=1 GROUP BY L
asked Nov 27, 2019 alecxe 7.5k points
0 votes
2 answers 132 views
132 views
Problem : I know that my query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. SELECT DISTINCT mycity FROM Employees WHERE mycountry = N'USA' AND myregion = N'WA' ORDER BY mybirthdate; What is a reason for ... gives me some output, but not the error. Is it because SELECT DISTINCT mycity only gives the result set with cities and nothing else ?
asked Jan 17 jwilliam 3.9k points