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

Question

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.

use:.... group by Products.productNumber, wholesalePrice,retailPrice — apomene, Apr 29 '15 at 13:18
definitely not mysql - it couldnt care less if all the select fields are in the `group by` — pala_, Apr 29 '15 at 13:19
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:32

score 4 · Accepted Answer · answered Apr 29 '15 at 13:17

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

score 1 · Answer 2 · answered Apr 29 '15 at 13:18

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

score 0 · Answer 3 · answered Apr 29 '15 at 13:19

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

score 0 · Answer 4 · answered Apr 29 '15 at 13:23

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

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

4 Answers4

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