You are currently viewing [SOLVED] Teradata Error 3504 SQLState HY000 Selected Non-Aggregate Values Must Be Part Of The Associated Group
Could You Please Share This Post? I Appreciate It And Thank YOU! :) Have A Nice Day!
4.9
(713)

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.

Problem -> Teradata Error 3504 SQLState HY000

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:

  1. SELECT <table1.attributes>, AVG (attribute)
  2. FROM table1
  3. JOIN (INNER JOIN / LEFT / RIGHT / FULL OUTER JOIN) table2
  4. ON table1.attribute = table2.attribute
  5. WHERE <condition>
  6. GROUP BY <attributes>
  7. HAVING <condition>
  8. 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.

Selected Non-Aggregate Values Must Be Part Of The Associated Group – Means 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)

Solution

[SOLVED] Teradata Error 3504 SQLState HY000 Selected Non-Aggregate Values Must Be Part Of The Associated Group

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;
Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

BigData-ETL: image 7YOU MIGHT ALSO LIKE

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 713

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?