Selected non-aggregate values must be part of the associated group

Database
Teradata Employee

Selected non-aggregate values must be part of the associated group

Why is the below query returning :"Selected non-aggregate values must be part of the associated group. " error?

SELECT /* UNION ALL JI */ item.item_id,

    SUM(cnt)

    FROM

(

    SELECT item.item_id,

        COUNT(*) AS cnt

        FROM item,

            revw,

            OS_Uma.price_greater_forty AS price

        WHERE item.item_id = revw.item_id

        AND  revw.item_id = price.item_id

UNION ALL

    SELECT web_page.item_id,

        COUNT(*) AS cnt

        FROM web_page,

            item

        WHERE item.item_id = web_page.item_id

        GROUP BY web_page.item_id, cnt) DT(item_id, cnt)

    GROUP BY 1;


Accepted Solutions
Teradata Employee

Re: Selected non-aggregate values must be part of the associated group

The first Select in the UNION is missing a Group By; the second one groups by too many columns.  They could all just say "Group By 1."

 

SELECT item.item_id, SUM(cnt)
FROM
( SELECT item.item_id,
        COUNT(*) AS cnt
        FROM item, revw, OS_Uma.price_greater_forty AS price
        WHERE item.item_id = revw.item_id
        AND  revw.item_id = price.item_id
        GROUP BY item.item_id
UNION ALL
    SELECT web_page.item_id,
        COUNT(*) AS cnt
        FROM web_page, item
        WHERE item.item_id = web_page.item_id
        GROUP BY web_page.item_id
) DT(item_id, cnt)
    GROUP BY 1;

1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: Selected non-aggregate values must be part of the associated group

The first Select in the UNION is missing a Group By; the second one groups by too many columns.  They could all just say "Group By 1."

 

SELECT item.item_id, SUM(cnt)
FROM
( SELECT item.item_id,
        COUNT(*) AS cnt
        FROM item, revw, OS_Uma.price_greater_forty AS price
        WHERE item.item_id = revw.item_id
        AND  revw.item_id = price.item_id
        GROUP BY item.item_id
UNION ALL
    SELECT web_page.item_id,
        COUNT(*) AS cnt
        FROM web_page, item
        WHERE item.item_id = web_page.item_id
        GROUP BY web_page.item_id
) DT(item_id, cnt)
    GROUP BY 1;

Teradata Employee

Re: Selected non-aggregate values must be part of the associated group

Thanks for the solution! That worked.

Does that mean that all the queries union all must have 'group by'? For example, the below example will fail as the first query doesn't have 'group by'?

Select * from

(Query1 (no group by)

Union All

Query 2(group by))DT()

Group by 1;

Teradata Employee

Re: Selected non-aggregate values must be part of the associated group

Group By is required for each select that includes an aggregate, like Sum() or Count().  If Query1 doesn't have an aggregate, then it doesn't need a Group-By.

Teradata Employee

Re: Selected non-aggregate values must be part of the associated group

Thank you for the explanation!

Junior Contributor

Re: Selected non-aggregate values must be part of the associated group

Depending on the actual PK/FK relations beween those tables you might be able the remove the join to item :

SELECT item.item_id, SUM(cnt)
FROM
( SELECT revw.item_id,
        COUNT(*) AS cnt
        FROM revw  -- switching to explicit instead of outdated explicit join
JOIN OS_Uma.price_greater_forty AS price ON revw.item_id = price.item_id GROUP BY 1 UNION ALL SELECT web_page.item_id, COUNT(*) AS cnt FROM web_page GROUP BY 1 ) DT(item_id, cnt) GROUP BY 1;

 

Senior Apprentice

Re: Selected non-aggregate values must be part of the associated group

...and in all of the examples above, the code will not run. It fails with "3807 Object 'Item' does not exist".

 

The outer query starts "SELECT item.item_id..." but the derived table that this SELECT reads from is called DT1.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Selected non-aggregate values must be part of the associated group

Hi Dave,

thanks for catching this, but it's even worse.

The query will not fail, because there's a table named item and it's product joined to the Derived Table before the aggregation.

So the query will either fail with a no more spool space or return a stupid result set with the same (huge) count for each row.

Highlighted
Senior Apprentice

Re: Selected non-aggregate values must be part of the associated group

Spooky! I'd just realised that the query might run, but with the issues that you point out.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com