Using qualify and group by together

UDA

Using qualify and group by together

I am using Teradata Sql Assistant 7.0

I want to execute the following query:

SELECT
Column1
,Column2
,Column3
,Column4
,Column5
,Column6
,
CASE
WHEN Column6 = 'N'
THEN 1
ELSE 2
END Column7
FROM
Table
WHERE
Column5 BETWEEN '2009/01/01'
AND '2009/06/30'
QUALIFY ROW_NUMBER () OVER ( PARTITION BY
Column1
,Column2
,column8
,Column9
,Column5
ORDER BY Column7) =1
GROUP BY 1,2,3,4,5,6,7

But the Query gets failed giving the following error:
"Selected non-aggregate values must be part of the associated group"

If i run the query with either Qualify or Group By it runs fine.
But its not accepting both Qualify and Group By together.
What should be the problem?
10 REPLIES
N/A

Re: Using qualify and group by together

Hi Hrushikesh,
OLAP functions are processed after GROUP BY/HAVING, you probably try to access a column which is neither in GROUP BY nor aggregated.

Could you post your actual query?

Dieter

Re: Using qualify and group by together

. Many of us facing this issue and this issue is due to software upgrade.
. what dnoeth said is correct.
. Here is the reasons and the resolutions to the Qualify and group by issue.
.
.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.

Re: Using qualify and group by together

Nice explanation

Re: Using qualify and group by together

What I found out, I think we cant use Qualify & Group By together. If we are using both, we need to put one in the inner query. Please check below conditions.

--Everything In Select clause, Giving Perfect answer

SEL STUDENT_ID,STUDENT_NAME,SUBJECT
FROM DB_NAME.STUDENT_DATA
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID ORDER BY SUBJECT)=1

--The Order By Column is not in Select clause, Still Giving Perfect answer

SEL STUDENT_ID,STUDENT_NAME
FROM DB_NAME.STUDENT_DATA
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID ORDER BY SUBJECT)=1

-- Aggregated column is used, but no group by.

-- Failed. 3504. Selected Non-aggregate values  must be part of the associate group

SEL STUDENT_ID,STUDENT_NAME,COUNT(SUBJECT)
FROM DB_NAME.STUDENT_DATA
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID ORDER BY SUBJECT)=1

-- Aggregated column is used, but no group by.

-- Failed. 3504. Selected Non-aggregate values  must be part of the associate group

SEL STUDENT_ID,STUDENT_NAME,COUNT(SUBJECT)
FROM DB_NAME.STUDENT_DATA
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID,STUDENT_NAME ORDER BY SUBJECT)=1

-- Aggregated column is used, Both Group by & Qualify is used together.

-- Failed. 3504. Selected Non-aggregate values  must be part of the associate group

SEL STUDENT_ID,STUDENT_NAME,COUNT(SUBJECT)
FROM DB_NAME.STUDENT_DATA
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID,STUDENT_NAME ORDER BY SUBJECT)=1
GROUP BY STUDENT_ID,STUDENT_NAME

-- Aggregated column is used, Both Group by & Qualify is used differently.

-- Giving Perfect answer

SEL STUDENT_ID,STUDENT_NAME,COUNT(SUBJECT)
FROM (
SEL STUDENT_ID,STUDENT_NAME,SUBJECT
FROM DB_NAME.STUDENT_DATA
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID,STUDENT_NAME ORDER BY SUBJECT)=1
)A
GROUP BY STUDENT_ID,STUDENT_NAME

Please correct me, if I am worng somewhere.

Re: Using qualify and group by together

I believe Group by and Qualify can be used together in a single query... You don't need to encapsulate in the subquery. your sequence of the clause is generating the error.

This query should work.

SEL STUDENT_ID,STUDENT_NAME,COUNT(SUBJECT)
FROM DB_NAME.STUDENT_DATA
GROUP BY STUDENT_ID,STUDENT_NAME
QUALIFY ROW_NUMBER() OVER( PARTITION BY STUDENT_ID,STUDENT_NAME ORDER BY SUBJECT)=1

Re: Using qualify and group by together

Hello Folks,

Im trying many work arounds for this issue, my requirement is :

we have a tool which fetches the "Select" clause columns dynamically based on user input, and onething to notice here is based on 'SELECT" clause, the "GROUP BY"will also vary for each different dynamic select clause.

For example, there are column1,column2,column3,column4. One user wants to view "column1,column2,column3", another user may want to view "column1,column4" and it keeps varying for each user needs. I need to structure a query in teradata in such a way that this happens dynamically at the query level. Please help me with this!

For now I have tried ;

SELECT (column1),(column2),(column3),(column4) FROM

(

We will have the main query logic given as sub query here

)X

group by X.column1,X.column2,X.column3,X.column4

So here the columns needed by the user can be passed to (column1),(column2),(column3),(column4),which is done during runtime.

However will this approach be effective if we have 50-60 columns involved.

Please suggest a optimized workaround.

Many thanks in advance!

N/A

Re: Using qualify and group by together

Well, this is the same as accessing a view with different Selects and everyone is doing that all the time.

The optimizer will do his best to eliminate unused columns.

Re: Using qualify and group by together

Thanks ton for your suggestion. These columns (column1),(column2),(column3),(column4) in the tool we use

will come as a "promt" to the users for them to enter their required "selected colums"similar to the "Viow Access" you are saying. But my concern here is, is there any Procedures or functions or cursors that we can use to handle this at the run time dynamically?

N/A

Re: Using qualify and group by together

Your "tool" should be able to create the needed SQL