asked
Group by
I could not understand why SUM() columns are not adding up. I get two rows instead of one with totals. I have used the function several times in the past, but this has stymied me. SELECT VirtualCenter ,VCL_Name ,PODName ,ISNULL(SUBSTRING(VirtualCenter,1,3),'Other')+ ''+ ISNULL(CAST(PODName as Varchar (2)),'Other') Location ,provisionstate ,CollectedDate Collected_Date ,HostsInMaintenanceMode Hosts_Maintenance ,SUM(HostsInCluster) Hosts_Infarm ,SUM(HostsInCluster) - SUM(HostsInMaintenanceMode) hosts_active ,CASE WHEN (HostsInCluster > 16) THEN 2 ELSE 1 END Hosts_Spare ,CASE WHEN (HostsInCluster - HostsInMaintenanceMode) = 0 THEN 0 ELSE HostsInCluster * TotalCPUCores/(HostsInCluster - HostsInMaintenanceMode)END vcpu_total_infarm ,SUM(vCPUs) vcpu_used_poweron ,CASE WHEN (HostsInCluster - HostsInMaintenanceMode) = 0 OR Mem_MB = 0 THEN 0 ELSE Mem_MB/1024/(HostsInCluster - HostsInMaintenanceMode) END mem_per_host ,CASE WHEN Mem_MB = 0 OR (HostsInCluster - HostsInMaintenanceMode) = 0 THEN 0 ELSE Mem_MB/1024/(HostsInCluster - HostsInMaintenanceMode)* HostsInCluster END mem_total_infarm ,(vRam_MB / 1024) mem_configured_poweron ,(DSCapacity_Gb / 1024) storage_total_infarm ,(DSCapacity_Gb / 1024 )* 0.9 storage_total_real ,(DSCapacity_Gb / 1024)- (DSFreeSpace_Gb / 1024) storage_used_poweron ,DSFreeSpace_Gb / 1024 DSFreeSpace ,CASE WHEN DSCapacity_Gb =0 OR DSFreeSpace_Gb = 0 THEN 0 ELSE ((DSCapacity_Gb / 1024) - DSFreeSpace_Gb /1024)/((DSCapacity_Gb / 1024 )* 0.9) * 100 END storage_used_real_percent FROM [dbo].[DSCPU] WHERE Date BETWEEN '10/20/2014' AND '10/21/2014' GROUP BY VirtualCenter ,VCL_Name ,PODName ,provisionstate ,CollectedDate ,HostsInMaintenanceMode ,HostsInCluster ,Mem_MB ,vRam_MB ,DSCapacity_Gb ,DSFreeSpace_Gb
Hello, You will get a sum for each of the groups - however I note that you have done a sum on a column (HostsInCluster) which is then in the GROUP BY clause. SQL Server is doing what you asked in that it is then grouping by each HostsInCluster value, and summing them (effectively multiplying the HostsInCluster value by the number of rows that uniquely share the grouped values). I suspect this is why it appears to not be summing the rows. Hope that helps.
15 People are following this question.
