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

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.

9 7 4
38,600 points

Related questions

0 votes
1 answer 34 views
34 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 36 views
36 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 60 views
60 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
1 answer 14 views
14 views
Problem: Hello! I am learning SQL. I came across the following statement while attempting a self-assessment quiz: You use aggregate functions to perform calculations based on groups of records (True/False) I do not know Aggregate functions so I skipped this question. Can someone from here please explain what the above statement means? Moreover, please explain the aggregated functions. Thanks!
asked Jan 17 Code Learner 5.5k points
0 votes
2 answers 195 views
195 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, 2020 jwilliam 3.9k points
0 votes
1 answer 2K views
2K views
Problem : My DbContext class defined the principal entity as below. HasOne(b => b.Customer).WithOne(c => c.BankAccount).HasForeignKey<BankAccount>(f => f.Id); But while running the update-database I am getting the following error: System.InvalidOperationException: To ... deleted the column, constraints and table as well and then the complete database also. But still I am getting the same error.
asked Nov 26, 2019 alecxe 7.5k points
0 votes
1 answer 202 views
202 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
2 answers 121 views
121 views
Problem : I am new to the SQL. What am I doing wrong here? SELECT LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%',batchinfo. ... I am trying to do group by the first, second, and third columns having the max rid. It is works perfectly fine without the group by and having.
asked Nov 27, 2019 alecxe 7.5k points
0 votes
1 answer 7 views
7 views
The login is from an untrusted domain and cannot be used with Windows authentication. Whenever I try to connect to a SQL server database, I get this error. Can you give me some advice?
asked Dec 1, 2020 miki 1.9k points