How to Solve 'column 'wmname' must appear in GROUP BY clause or be used in an aggregate function' in SQL?

Resolving SQL error: "Columns must appear in a GROUP BY clause or be used as an aggregate function"

In SQL query, if the select field is neither included in the GROUP BY clause nor any aggregation operation is performed, "Column 'wmname' must appear in the GROUP BY clause or be used as an aggregate function. " error message.

Specifically, you are trying to find the maximum average (avg) for each country (cname) while returning the corresponding country manager name (wmname). However, wmname is neither included in the GROUP BY clause nor used in any aggregate function.

Possible solutions:

There are several ways to solve this problem:

Method 1: Using subqueries and joins

This method involves creating a subquery to calculate the maximum average for each country and then joining the result with the original table to contain the names of the country managers.

<code class="language-sql">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;</code>

Method 2: Use window function

Window functions provide a way to obtain this result without using a subquery.

<code class="language-sql">SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar;</code>

Method 3: Use DISTINCT and ROW_NUMBER()

A more explicit approach is to use DISTINCT to ensure unique results and ROW_NUMBER() to select only the rows with the highest average per country.

<code class="language-sql">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 cname ORDER 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;</code>

By using one of these methods you can retrieve the expected results where the country manager name (wmname) is correctly associated with the maximum average for each country. Please pay attention to the ROW_NUMBER() clause of ORDER BY in method three, which is crucial for the correct selection of the maximum average. Previous versions were missing this clause, which could lead to incorrect results.

The above is the detailed content of How to Solve 'column 'wmname' must appear in GROUP BY clause or be used in an aggregate function' in SQL?. For more information, please follow other related articles on the PHP Chinese website!