CODEWITHSUNDEEP
sqlsql-server
Jason Mallory
Jason Mallory

Reputation: 9

Group By Error in SQL Server SQL Server 2016

I am trying to run the following query:

select en.NAME as ClusterName, ds.NAME , 
ROUND((CAST(ds.capacity as float))/1024/1024/1024,2) as
Total_Allocated_GB,
ROUND((CAST(ds.FREE_SPACE as float))/1024/1024/1024,2)as Free_Space_GB
from dbo.VPXV_DATASTORE DS, dbo.VPXV_HOST_DATASTORE HSDS, VPXV_ENTITY 
en, VPXV_HOSTS hs
where
HSDS.DS_ID = DS.ID  and
hs.HOSTID  = hsds.HOST_ID and
en.id = hs.FARMID
GROUP BY en.NAME

I get the following error:

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

How can I get these to group by en.NAME?

Upvotes: 0

Views: 56

Answers (1)

SqlZim
SqlZim

Reputation: 38043

You have to group by columns that are not being aggregated. I am guessing you want the sum() those two calculations and also group by en.name and ds.name. 

select 
    ClusterName = en.name
  , ds.name
  , Total_Allocated_gb = sum(round((cast(ds.capacity as float)) / 1024 / 1024 / 1024, 2))
  , Free_Space_gb = sum(round((cast(ds.free_space as float)) / 1024 / 1024 / 1024, 2))
from dbo.vpxv_datastore ds
  inner join dbo.vpxv_host_datastore hsds 
    on hsds.ds_id = ds.id 
  inner join vpxv_hosts hs 
    on hs.hostid = hsds.host_id 
  inner join vpxv_entity en 
    on en.id = hs.farmid
group by 
    en.name
  , ds.name

Upvotes: 1

Related Questions