Skip to content
Advertisement

SELECT list is not in GROUP BY error but I got a result cleary

here is a code that I made.

SELECT
    substr(to_char(birthday, 'YYYY'), 1, 3)
    || '0',
    emp_type,
    AVG(pay)
FROM
emp2
WHERE
    substr(to_char(birthday, 'YYYY'), 1, 3) IN (
        '196',
        '197',
        '198',
        '199'
    )
GROUP BY
    substr(to_char(birthday, 'YYYY'), 1, 3),
    emp_type;

The code is simple, just group people by 1960s, 1970s ,1980s ,1990s and find an average pay by using AVG(PAY)

There is no problem or warnings when I run this code, but Oracle SQL Devloper shows me this. warning sign

Sorry for my language. Anyway, the warning said the SELECT list is not same as GROUP BY.

I know this is a minor stuff ,but I think the one I SELECT is definitely in GROUP BY. Please tell me whether I am right or wrong about my thought. And here is the result and data table used in this code.

result enter image description here

Advertisement

Answer

Your group by needs to contain the columns you selected. In your case, you selected 2 columns:

SELECT 
    substr(to_char(birthday, 'YYYY'), 1, 3) || '0',
    emp_type,
    ...

but you missed the concatenated ‘0’ in your group by clause, so the 2 columns in your group by are not the same as the ones in your select clause.

GROUP BY
    substr(to_char(birthday, 'YYYY'), 1, 3),
    emp_type;

Try changing that to

GROUP BY
    substr(to_char(birthday, 'YYYY'), 1, 3) || '0',
    emp_type;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement