SQL Server按USER_TYPE和ROLE_DESCRIPTION分组报错求解

Hey there! Let's break down how to resolve that GROUP BY error and get your query working exactly how you want it.

First, Why the Error Happens #

The error message you're seeing:

"Column 'USERS.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

This is because in standard SQL (and most databases running in strict mode), any column you include in your SELECT clause must either:

• Be listed in the GROUP BY clause (so the database knows to group rows by that column), or
• Be wrapped in an aggregate function (like MAX(), MIN(), COUNT()) to tell the database which value to pick for the group.

In your query, you're selecting U.id and U.USER_NAME but not including them in GROUP BY or using an aggregate function—so the database doesn't know which values to return for those columns when grouping by USER_TYPE and ROLE_DESCRIPTION.

Solutions Based on Your Data #

Looking at your sample data, every row has the same id (40406) and USER_NAME (AJ) for each combination of USER_TYPE and ROLE_DESCRIPTION. Here are two ways to fix this:

1. Add id and USER_NAME to the GROUP BY Clause

Since these values are consistent across each group, you can safely include them in your GROUP BY to satisfy the SQL rules. Your query would look like this:

SELECT U.id, U.USER_NAME, UT.USER_TYPE, R.ROLE_DESCRIPTION
FROM USERS U
-- Add your actual JOIN conditions here (e.g., U.id = UT.user_id, UT.role_id = R.id)
JOIN USER_TYPES UT ON [your join condition]
JOIN ROLES R ON [your join condition]
GROUP BY U.id, U.USER_NAME, UT.USER_TYPE, R.ROLE_DESCRIPTION;

This will return one row per unique combination of USER_TYPE and ROLE_DESCRIPTION, along with the matching id and USER_NAME.

2. Use Aggregate Functions (If Values Might Vary)

If in the future you might have multiple id/USER_NAME values for the same USER_TYPE + ROLE_DESCRIPTION group, use aggregate functions to specify which value to return. For example, to get the maximum id and corresponding username:

SELECT MAX(U.id) AS id, MAX(U.USER_NAME) AS USER_NAME, UT.USER_TYPE, R.ROLE_DESCRIPTION
FROM USERS U
JOIN USER_TYPES UT ON [your join condition]
JOIN ROLES R ON [your join condition]
GROUP BY UT.USER_TYPE, R.ROLE_DESCRIPTION;

Or if you want to list all related ids/usernames in a single column (database-specific):

• MySQL/MariaDB: Use GROUP_CONCAT()

  SELECT GROUP_CONCAT(DISTINCT U.id SEPARATOR ', ') AS ids,
         GROUP_CONCAT(DISTINCT U.USER_NAME SEPARATOR ', ') AS user_names,
         UT.USER_TYPE, R.ROLE_DESCRIPTION
  FROM USERS U
  JOIN USER_TYPES UT ON [your join condition]
  JOIN ROLES R ON [your join condition]
  GROUP BY UT.USER_TYPE, R.ROLE_DESCRIPTION;
  

• SQL Server: Use STRING_AGG()

  SELECT STRING_AGG(DISTINCT U.id, ', ') AS ids,
         STRING_AGG(DISTINCT U.USER_NAME, ', ') AS user_names,
         UT.USER_TYPE, R.ROLE_DESCRIPTION
  FROM USERS U
  JOIN USER_TYPES UT ON [your join condition]
  JOIN ROLES R ON [your join condition]
  GROUP BY UT.USER_TYPE, R.ROLE_DESCRIPTION;
  

Example Output (Matching Your Sample Data) #

With the first solution, your result will match your sample data exactly—one row for each unique USER_TYPE and ROLE_DESCRIPTION pair, with the consistent id and USER_NAME values.

内容的提问来源于stack exchange，提问作者AJ Moquete