Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have a table that looks like this caller 'makerar'

 cname  | wmname |          avg            --------+-------------+------------------------  canada | zoro   |     2.0000000000000000  spain  | luffy  | 1.00000000000000000000  spain  | usopp  |     5.0000000000000000 

And I want to select the maximum avg for each cname.

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

but I will get an 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; 

so i do this

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

however this will not give the intented results, and the incorrect output below is shown.

 cname  | wmname |          max            --------+--------+------------------------  canada | zoro   |     2.0000000000000000  spain  | luffy  | 1.00000000000000000000  spain  | usopp  |     5.0000000000000000 

Actual Results should be

 cname  | wmname |          max            --------+--------+------------------------  canada | zoro   |     2.0000000000000000  spain  | usopp  |     5.0000000000000000 

How can I go about fixing this issue?

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

like image 859
RandomGuy Avatar asked Oct 26 '13 01:10

RandomGuy


People also ask

Why column must appear in the GROUP BY clause or be used in an aggregate function?

Explanation: This error occurs since you've specified an aggregate function (e.g. MAX(...) ) alongside a regular column in the query's target list, but you didn't specify the regular column as one of the columns to be grouped by.

Can we use aggregate function in GROUP BY clause?

The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

Which clause is used with an aggregate function?

Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values.

Which clause is used whenever aggregate functions by group are required?

The GROUP BY clause is normally used along with five built-in, or "aggregate" functions. These functions perform special operations on an entire table or on a set, or group, of rows rather than on each row and then return one row of values for each group.


1 Answers

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

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

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 window functions, which looks simpler:

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

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

 cname  | wmname |          mx            --------+--------+------------------------  canada | zoro   |     2.0000000000000000  spain  | luffy  |     5.0000000000000000  spain  | usopp  |     5.0000000000000000 

The solution, arguably less elegant, to show the only (cname, wmname) tuples matching the max value, is:

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */     m.cname, m.wmname, t.avg AS mx FROM (     SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn      FROM makerar ) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1 ;    cname  | wmname |          mx            --------+--------+------------------------  canada | zoro   |     2.0000000000000000  spain  | usopp  |     5.0000000000000000 

[*]: Interestingly enough, even though the spec sort of allows to select non-grouped fields, major engines seem to not really like it. Oracle and SQLServer just don't allow this at all. Mysql used to allow it by default, but now since 5.7 the administrator needs to enable this option (ONLY_FULL_GROUP_BY) manually in the server configuration for this feature to be supported...

like image 199
Sebas Avatar answered Sep 23 '22 13:09

Sebas