In this post, I will explain why you encountered an error Teradata Error 3504 SQLState HY000 Selected non-aggregate values must be part of the associated group while executing the SQL query in Teradata. I will present the cause of the problem and show how to avoid the error in the future.
Error 3504 occurs when grouping data in Teradata using the GROUP BY statement. Whether you only write the DML (SELECT) query or use it during a DDL operation (e.g. CREATE VIEW), you’ll get the same error. Here is the easiest way to receive an error message:
SELECT EMPLOYEE_ID, AVG(SALARY) FROM EMPLOYEES ORDER BY 1;
The purpose of the above query is to calculate the average salary for all employees. However, something is probably missing …
No GROUP BY statement
During using grouping functions (e.g. SUM, COUNT, AVG), you must remember to apply the GROUP BY clause between the WHERE command and HAVING / ORDER BY. The order of the SQL operation is as follows:
- SELECT <table1.attributes>, AVG (attribute)
- FROM table1
- JOIN (INNER JOIN / LEFT / RIGHT / FULL OUTER JOIN) table2
- ON table1.attribute = table2.attribute
- WHERE <condition>
- GROUP BY <attributes>
- HAVING <condition>
- ORDER BY <attributes>
Exception – you do not have to use the GROUP BY clause during grouping data without using additional attributes, but it is used rarely.
Invalid number of attributes
The problem Selected non-aggregate values must be part of the associated group may also occur if you already have the GROUP BY clause in your query and it means that the list of arguments in the GROUP BY clause is incomplete, i.e. not all arguments that are used with grouping functions were included in GROUP BY line, e.g .:
SELECT EMPLOYEE_ID, YEAR, AVG(SALARY) FROM EMPLOYEES GROUP BY EMPLOYEE_ID ORDER BY 1,2;
In the above query, after the SELECT statement we have 2 attributes EMPLOYEE_ID and YEAR, but only the EMPLOYEE_ID attribute is used with GROUP BY clause – it will not work. (Teradata Error 3504 SQLState HY000)
Always use the GROUP BY clause for grouping functions
In practice, queries with grouping functions without additional attributes are very rarely used, e.g .:
SELECT SUM(SALARY) FROM EMPLOYEES;
Certainly they are helpful during tests or searching for general sums or numbers of e.g. clients from the beginning of the company’s existence. Usually, we need more complicated calculations. So always remember to include the GROUP BY clause.
Make sure the argument list is correct
Always make sure that the list of arguments after the SELECT clause match the list of attributes in the GROUP BY command, e.g .:
SELECT EMPLOYEE_ID, YEAR, AVG(SALARY) FROM EMPLOYEES GROUP BY EMPLOYEE_ID, YEAR ORDER BY 1,2;
If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!