Why Does My SQL Query Fail with 'must appear in the GROUP BY clause or be used in an aggregate function'?

Resolve SQL query error: "must appear in the GROUP BY clause or be used in an aggregate function"

In SQL, when performing aggregate operations (such as MAX, MIN, SUM, etc.), the columns in the SELECT list must appear in the GROUP BY clause, or be included in an aggregate function. This restriction ensures that results are grouped correctly and aggregate values ​​are calculated correctly.

For example, suppose your table contains cname, wmname, and avg columns, and you want to find the maximum cname value for each avg. Your original query is as follows:

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

This query fails because the wmname column is neither included in the GROUP BY clause nor used in an aggregate function. This means that the MAX aggregate function is evaluated without grouping by wmname, resulting in incorrect results.

The following methods can solve this problem:

Method 1: Include wmname in the GROUP BY clause:

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

This method returns the maximum cname value for each wmname and avg combination.

Method 2: Use subquery:

<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>

This method first calculates the maximum cname value for each avg using a subquery and then joins it with the original table to retrieve the corresponding wmname value.

Method 3: Use window function:

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

Window function MAX(avg) OVER (PARTITION BY cname) calculates the maximum cname value for each avg and retains all rows in the result. However, this method may display duplicate rows if there are multiple rows with the same maximum cname value for a given avg.

By correctly adhering to the requirement that non-aggregate function columns must appear in the GROUP BY clause, you can ensure that aggregate queries produce accurate and meaningful results.

The above is the detailed content of Why Does My SQL Query Fail with 'must appear in the GROUP BY clause or be used in an aggregate function'?. For more information, please follow other related articles on the PHP Chinese website!