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?
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP