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