Using QUALIFY and GROUP BY together gives 3504 ERROR: SELECTED NON-AGGREGATE VALUES MUST BE PART OF THE ASSOCIATED GROUP???

Database
Enthusiast

Using QUALIFY and GROUP BY together gives 3504 ERROR: SELECTED NON-AGGREGATE VALUES MUST BE PART OF THE ASSOCIATED GROUP???

. Here is the reasons and the resolutions to the Qualify and group by issues.
.
.3504 ERROR: SELECTED NON-AGGREGATE VALUES MUST BE PART OF THE ASSOCIATED GROUP.
.
.This error occurs when an aggregate query includes a non-aggregate expression in the SELECT list, WHERE clause, ORDER BY clause, HAVING clause or WITH list that is not also exactly specified in the corresponding GROUP BY or WITH...BY clause. Note that the optimizer also return this error when ORDER BY and WITH clauses contain aggregates but the query does not.
.
.***************************************************** **********************
.What is the relation between 3504 error and Qualify Row_number() function?
.
.Qualify Row_number() function will always involve PARTITION BY and ORDER BY clauses. Considering the above error .description, we can say, this error will be thrown only when QUALIFY is used along with GROUP BY. In such cases, the only .possibility for the error to occur is any mistake commited while coding the “ORDER BY” clause of “QUALIFY”.
.
.Case 1: (Without aggregate function)
.
.SEL EMP_NO,
. EMP_NAME
.
.FROM DB_NAME.EMP_DETAILS
.WHERE JOIN_DATE = 11008
.
.QUALIFY ROW_NUMBER()
.OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO) = 1
.
.GROUP BY 1,2;
.
. When we attempt to execute the above query, 3504 error is thrown. This is because the ORDER BY is based on DEPT_NO, .which is not part of the SELECT list. To talk in more technical terms, DEPT_NO is not a part of grouping. That is why it .is considered as a non aggregate expression and hence the error.
.
.Resolution:
.SEL EMP_NO,
. EMP_NAME,
. DEPT_NO
.
.FROM DB_NAME.EMP_DETAILS
.WHERE JOIN_DATE = 11008
.
.QUALIFY ROW_NUMBER()
.OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO) = 1
.
.GROUP BY 1,2,3;
.
.Case 2: (With aggregate function)
.
.SEL EMP_NO,
. EMP_NAME,
. Max(DEPT_NO)
.
.FROM DB_NAME.EMP_DETAILS
.WHERE JOIN_DATE = 11008
.
.QUALIFY ROW_NUMBER()
.OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO) = 1
.
.GROUP BY 1,2;
.
. When we attempt to execute the above query, 3504 error is thrown. This is because the ORDER BY is based on DEPT_NO, .which is not part of the SELECT list. Please note MAX(DEPT_NO) is different from DEPT_NO. MAX(DEPT_NO) is an aggregate .field whereas DEPT_NO is just a non-aggregate field. Going with the error description, you could notice the non-aggregate .field DEPT_NO is used as a part of ORDER BY clause in QUALIFY. This is against the rule and hence the error.
.
.Resolution 1:
.SEL EMP_NO,
. EMP_NAME,
.Max(DEPT_NO) AS MAX_DEPT_NO
.
.FROM DB_NAME.EMP_DETAILS
.WHERE JOIN_DATE = 11008
.
.QUALIFY ROW_NUMBER()
.OVER (PARTITION BY EMP_NO ORDER BY MAX_DEPT_NO) = 1
.
.GROUP BY 1,2;
.
.Resolution 2:
.SEL EMP_NO,
. EMP_NAME,
. DEPT_NO,
. Max(DEPT_NO)
.
.FROM DB_NAME.EMP_DETAILS
.WHERE JOIN_DATE = 11008
.
.QUALIFY ROW_NUMBER()
.OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO) = 1
.
.GROUP BY 1,2,3;
.
.Selecting between these two resoultions is dependent on the requirement.
****************************************************** *********************
.
.Is the 3504 error a threat only to QUALIFY??
.
.This error is not a threat only to the combination of QUALIFY and GROUP BY. The following combinations are also affected.
.
.1. COUNT WINDOW and GROUP BY.
.2. RANK WINDOW and GROUP BY.
.
.The reasons for such errors and the associated resolutions remain the same.
.
.Here is a sample for COUNT WINDOW/GROUP BY combination.
.
.SEL
. EMP_NO,
. EMP_NAME,
. COUNT(*) OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO) /*COUNT WINDOW*/
.
. FROM DB_NAME.EMP_DETAILS A
.
. WHERE
. A.JOIN_DATE = 11008
.GROUP BY 1,2;
.
.The above query will throw 3504 error.
.
.Resolution:
.SEL
. EMP_NO,
. EMP_NAME,
. DEPT_NO,
. COUNT(*) OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO)
.
. FROM DB_NAME.EMP_DETAILS A
.
. WHERE
. A.JOIN_DATE = 11008
.
.GROUP BY 1,2,3;
.
.Here is a sample for RANK WINDOW/GROUP BY combination.
.
.SEL
. EMP_NO,
. EMP_NAME,
. RANK() OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO) /*COUNT WINDOW*/
.
. FROM DB_NAME.EMP_DETAILS A
.
. WHERE
. A.JOIN_DATE = 11008
.GROUP BY 1,2;
.
.The above query will throw 3504 error.
.
.Resolution:
.SEL
. EMP_NO,
. EMP_NAME,
. DEPT_NO,
. RANK() OVER (PARTITION BY EMP_NO ORDER BY DEPT_NO)
.
. FROM DB_NAME.EMP_DETAILS A
.
. WHERE
. A.JOIN_DATE = 11008
.
.GROUP BY 1,2,3;

. Hope this helps you.
2 REPLIES
Enthusiast

Re: Using QUALIFY and GROUP BY together gives 3504 ERROR: SELECTED NON-AGGREGATE VALUES MUST BE PART OF THE ASSOCIATED GROUP???

Well put. I've also been walking about with this bit of code as a primer:

SELECT last_name
,salary_amount
,department_number
, AVG(salary_amount) ave_sal2
,COUNT(*) OVER (PARTITION BY department_number ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS a
FROM employee, (SEL CAST(AVG(salary_amount)AS DECIMAL(10,2)) FROM employee) temp (avg_sal)
WHERE department_number <302 -- use for exclusion /inclusion filtering
HAVING ave_sal2 >60000.00 --use for aggregate function filtering
QUALIFY a <=3 -- used for OVER conditions
GROUP BY 1,2,3 -- required for aggregate functions
Enthusiast

Re: Using QUALIFY and GROUP BY together gives 3504 ERROR: SELECTED NON-AGGREGATE VALUES MUST BE PART OF THE ASSOCIATED GROUP???

Hi,

When i try to execute the below query , i get 3504 error:

SELECT a, TRIM(b) AS b, c, TRIM(d) AS d
GROUP BY 1,2,3,4
FROM db1.tbl1
QUALIFY RANK() OVER (PARTITION BY a, b, c ORDER BY SUM(tot) DESC) = 1

But when i change the Alias from b to b1 and use it in PARTITION it is working fine.

SELECT a, TRIM(b) AS b1, c, TRIM(d) AS d
GROUP BY 1,2,3,4
FROM db1.tbl1
QUALIFY RANK() OVER (PARTITION BY a, b1, c ORDER BY SUM(tot) DESC) = 1

Can you explain this behaviour?