Sql Error Msg 8120

Prashant Nair

15y6.8kSQL

Hi,

I am getting erorr sql stored procedure

Code

Select ProductID,ProductName,ProductCategoryName,Sum(UnitsInStock),ProductImageID,SUBSTRING(Description,1,150) + '...' As Description,Price,DeliveryDate from Products INNER JOIN ProductCategory ON ProductCategory.ProductCategoryID = Products.ProductCategoryID

Error Message

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

How to fix this ?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Dwivedi15y ago
Accepted answer
Hi Prasant ,
you have used the aggregate function SUM in the select, so you have to use Group By clause for each selected column like that

Select ProductID,ProductName,ProductCategoryName,Sum(UnitsInStock),ProductImageID,SUBSTRING(Description,1,150) + '...' As Description,Price,DeliveryDate from Products INNER JOIN ProductCategory ON ProductCategory.ProductCategoryID = Products.ProductCategoryID group by ProductID,ProductName,ProductCategoryName,ProductImageID,Description,Price,DeliveryDate

by this way you can remove this error.
Krishna Garad15y ago
Give aliases to your table i.e. product and productcategory or you can select the fields with table name as well like bellow product.ProductId, product.ProductName,product.ProductCategoryName etc...