CODEWITHSUNDEEP
sql-serverinner-join
user4846532
user4846532

Reputation:

SQL INNER JOIN - Column name in join is also in select *error*

I'm still pretty new to SQL and am having issues. I am using a variation of the classic Northwind Database. I am using Microsoft SQL Server Management Studio.

Here is the problem:

/*
Join Products and ProductVendors to display by product, the product
number, the average wholesale price, and the average retail price.
*/

Here's what I've got:

SELECT Products.productNumber As [Product Number],
       CAST(AVG(wholesalePrice) as decimal (8,2)) As [Average Wholesale Price],
       CAST(AVG(retailPrice) as decimal (8,2))  As [Average Retail Price]
FROM Products INNER JOIN ProductVendors 
       ON Products.productNumber = ProductVendors.productNumber

Here's the error I get:

Msg 8120, Level 16, State 1, Line 2
Column 'Products.productNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 90

Answers (4)

BrainCoder
BrainCoder

Reputation: 5337

If you use AGGREGATE functions then GROUP BY clause must be specified, here you are selecting ProductNumber so specify GROUP BY clause on that

SELECT Products.productNumber As [Product Number],
       CAST(AVG(wholesalePrice) as decimal (8,2)) As [Average Wholesale Price],
       CAST(AVG(retailPrice) as decimal (8,2))  As [Average Retail Price]
FROM Products 
INNER JOIN ProductVendors ON Products.productNumber = ProductVendors.productNumber
GROUP BY Products.productNumber

Upvotes: 0

Saravana Kumar
Saravana Kumar

Reputation: 3729

If you use AGGREGATE functions then GROUP BY clause must be specified.

SELECT Products.productNumber As [Product Number],
       CAST(AVG(wholesalePrice) as decimal (8,2)) As [Average Wholesale Price],
       CAST(AVG(retailPrice) as decimal (8,2))  As [Average Retail Price]
FROM Products 
INNER JOIN ProductVendors ON Products.productNumber = ProductVendors.productNumber
GROUP BY Products.productNumber

Upvotes: 0

Jens
Jens

Reputation: 69440

If you want to use aggregation functions, you must have a group by statement.

SELECT Products.productNumber As [Product Number],
       CAST(AVG(wholesalePrice) as decimal (8,2)) As [Average Wholesale Price],
       CAST(AVG(retailPrice) as decimal (8,2))  As [Average Retail Price]
FROM Products INNER JOIN ProductVendors ON Products.productNumber = ProductVendors.productNumber
group by Products.productNumber

An the group by statement must contain all columns wich are not in a aggregation function

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

Exception text is self-explanatory. Since you're using aggregate functions (avg in your case) you have to group by Products.productNumber too.

select Products.productNumber aa [Product Number],
       CAST(AVG(wholesalePrice) as decimal (8,2)) as [Average Wholesale Price],
       CAST(AVG(retailPrice) as decimal (8,2))  as [Average Retail Price]
from Products inner join ProductVendors 
    on Products.productNumber = ProductVendors.productNumber
group by Products.productNumber

Upvotes: 4

Related Questions