Column is not contained in either an aggregate function or the GROUP BY clause

Question

I have to tables: DeviceInstace and Device

They are connected with Foreign Key on DeviceInstance.DeviceId=Device.Id

I have SQL query:

select d.CatalogNo,d.Manufacturer,d.Name, sum(quantity) 
from DeviceInstance di 
full join Device d 
on d.Id=di.DeviceId 
group by di.DeviceId

with which I need to make little summary that consist of:

• catalog number
• manufacturer
• device name
• number of devices (sum of values in quntity columns for these deviceIds)

But I'm facing some Grouping by issuses.

All I try refuse me error like this one:

Column 'Device.CatalogNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know I can group by CatalogNo but then ten sum of quantity will return wrong numbers.

Can anyone suggest me how to repair it?

Answer 1

You need to Add d.CatalogNo,d.Manufacturer in Group By Clause

Try this

select d.CatalogNo,d.Manufacturer,d.Name, sum(quantity) 
from DeviceInstance di 
full join Device d 
on d.Id=di.DeviceId 
group by d.Name, d.CatalogNo,d.Manufacturer

Answer 2

Just modify GROUP BY clause:

group by d.DeviceId, d.CatalogNo,d.Manufacturer,d.Name