Why Does 'Column Must Appear in GROUP BY Clause or be Used in an Aggregate Function' Occur in SQL Queries?

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

When executing a grouped aggregation query, you must ensure that the selected columns either appear in the GROUP BY clause or participate in the calculation of the aggregate function. Otherwise, an error message similar to "Column must appear in a GROUP BY clause or be used as an aggregate function" will appear.

Problem Description

Suppose there is a table named makerar with columns cname, wmname, and avg. The goal is to get the maximum cname value for each distinct avg . However, execute the following query:

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

will throw an error because the wmname column is neither included in the GROUP BY clause nor used as an aggregate function.

Solution

There are two main ways to solve this problem:

1. Subquery and join method

This method uses a subquery to calculate the maximum cname value for each avg and then joins the subquery with the main table to retrieve the corresponding wmname:

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
;

2. Window function method

Window functions provide a simpler alternative to perform aggregate calculations within groups:

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

Note: While the window function correctly displays the maximum cname value for each avg, it may result in duplicate records.

The above is the detailed content of Why Does 'Column Must Appear in GROUP BY Clause or be Used in an Aggregate Function' Occur in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!