How to Solve the SQL 'Column Must Appear in GROUP BY Clause' Error?

Resolving "Error: Column must appear in GROUP BY clause"

In SQL, when performing an aggregation operation (such as finding the maximum value), the columns used for aggregation must also appear in the GROUP BY clause. Failure to do so will result in the error: "Column must appear in a GROUP BY clause or be used as an aggregate function".

Suppose you want to find the maximum average value (avg) for each customer name (cname) in the table:

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

This query will return an error because wmname is not included in the GROUP BY clause. To fix this you can simply group by cname and wmname:

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

However, this method does not produce the expected result of showing the maximum avg value for each unique cname. Instead, it will be grouped by cname and wmname, resulting in multiple rows per cname. To correct this issue, follow one of the following methods:

Using subqueries and joins

1. Calculate the maximum avg value for each cname in a subquery:

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

2. Join the subquery with the original table to retrieve the required columns:

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;

Use window functions

Window functions allow you to perform calculations across rows within a specified window. In this case you can use PARTITION BY clause to group by cname and calculate the maximum avg value in each partition:

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

This method will display all records, but it will correctly display the maximum avg value per cname per row.

Handle matching unique tuples

If you only want to display the unique tuples matching the maximum avg value, you can use the following method:

1. Use the ROW_NUMBER() function to calculate the ranking of avg values ​​within each cname partition:

SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY cname ORDER BY avg DESC) AS rn 
FROM makerar;

2. Join the result with the original table to filter rn = 1 (the first ranked tuple):

SELECT DISTINCT /* distinct matters here */
    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;

This method will return a list of unique tuples containing the maximum avg value for each cname. Note the addition of ORDER BY avg DESC to ensure that the ranking is in descending order based on avg.

The above is the detailed content of How to Solve the SQL 'Column Must Appear in GROUP BY Clause' Error?. For more information, please follow other related articles on the PHP Chinese website!