TransWikia.com

Not using GROUP BY but getting Invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Stack Overflow Asked on December 30, 2021

I am using SQL Server 2014. I’m familiar with resolving this particular error when I am using a GROUP BY, but in this case I am not using a GROUP BY and I’m not sure what the proper way to resolve this. I could wrap everything in the select with MAX(), but that’s not ideal, especially as my actual query has many more selects than this simplified query shown below (note, the simplified query still replicates the error).

SELECT TOP 100
    items.item_pk,
    SUM(options.price * options.quantity) AS opt_price,
FROM
    dbo.items
LEFT JOIN 
    options ON qitem_fk = qitem_pk;

I get this error:

Column ‘items.item_pk’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

What can I do to resolve this issue?

3 Answers

You could try making use of SUM() OVER (PARTITION BY ORDER BY) syntax. This syntax is considered to be an analytical function however it produces an aggregate calculation. This allows you to produce the values you want outside of your result set however the calculations are of course based on the data being returned within the result set. It allows you to apply an aggregate calculation to a query that doesn't have any aggregate functions.

SELECT
    items.item_pk,
    SUM(options.price * options.quantity) OVER (PARTITION BY items.item_pk ORDER BY items.item_pk) AS opt_price,

FROM dbo.items
    LEFT JOIN 
        options ON qitem_fk = qitem_pk

Here's a link that explains analytical and aggregate functions nicely. The link points to Oracle but it's mostly the same with any database.

Answered by Code Novice on December 30, 2021

I have found doing a join on a subquery gives me the behavior required. This is because I am now joining on a table where the aggregation is already performed. It may take a big performance hit, but in my particular situation it is acceptable.

SELECT 
    items.*, 
    option_aggregate.opt_price 
FROM 
    dbo.items 
    LEFT JOIN (
        SELECT 
            SUM(
                options.price * options.quantity
            ) AS opt_price, 
            item_fk 
        FROM 
            options 
        GROUP BY 
            item_fk
    ) AS option_aggregate ON option_aggregate.item_fk = items.item_pk

Answered by Goose on December 30, 2021

If you are using an aggregate SUM on some columns, columns which do not have aggregate function needs to be included in the group by clause.
So you need to add a GROUP BY to your query:

SELECT TOP 100
    items.item_pk,
    SUM(options.price * options.quantity) AS opt_price,
FROM
    dbo.items
LEFT JOIN 
    options ON qitem_fk = qitem_pk
GROUP BY items.item_pk;

Answered by Piotr on December 30, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP