Selected non-aggregate values must be part of the associated group. SELECT Command Failed

user_11578552 0
Viewed 121

I am using the below query and it gives error that "Selected non-aggregate values must be part of the associated group. SELECT Command Failed."

SELECT TOP 100 X_ISP_AFF_ADDR_SEQ, CAST(COUNT(*) AS BIGINT) AS COUNT_ROW FROM S_CONTACT WHERE X_ISP_AFF_ADDR_SEQ NOT LIKE '%[a-zA-Z]%';

I changed the above query as written below but the error still persists.

SELECT TOP 100 X_ISP_AFF_ADDR_SEQ, CAST(COUNT(X_ISP_AFF_ADDR_SEQ) AS BIGINT) AS COUNT_ROW FROM S_CONTACT WHERE X_ISP_AFF_ADDR_SEQ NOT LIKE '%[a-zA-Z]%';

teradata
1 Answers

You presumably are missing a GROUP BY clause here:

SELECT TOP 100
    X_ISP_AFF_ADDR_SEQ,
    CAST(COUNT(*) AS BIGINT) AS COUNT_ROW
FROM S_CONTACT
WHERE
    X_ISP_AFF_ADDR_SEQ NOT LIKE '%[a-zA-Z]%'
GROUP BY
    X_ISP_AFF_ADDR_SEQ;

The exact error you were seeing with your original query has to do with that selecting X_ISP_AFF_ADDR_SEQ instructs Teradata to return a value for each record in the table, whereas COUNT() returns a value over the entire table. It is not possible (in general) to mix aggregates and non aggregates in a select clause.

user_18632290
Related