SQL sum by group name with inner join present

Question

I have following SQL scrip:

  SELECT       
    vapp.NAME
  , memcpu.[MEM_SIZE_MB]/1000
  , memcpu.[NUM_VCPU]
  , sum(convert(bigint, hdd.CAPACITY))
  FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
  INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
  ON memcpu.ID = hdd.VM_ID
  INNER JOIN [VCenterServer].[dbo].[VPXV_RESOURCE_POOL] AS vapp
  ON memcpu.RESOURCE_GROUP_ID = vapp.RESOURCEPOOLID
  GROUP BY 
  memcpu.[ID]
  , memcpu.[DNS_NAME] 
  , memcpu.[MEM_SIZE_MB]
  , memcpu.[NUM_VCPU]
  , hdd.PATH
  , hdd.CAPACITY
  , vapp.RESOURCEPOOLID
  , vapp.NAME;

It porduces this result

NAME      MEM     NUM_VCPU  CAPACITY
Core       3            1   42947571712
Core       2            1   42947571712
Core       4            1   42947571712
Core       4            1   134214578176
Phones     2            2   26977628160
Phones     2            2   103442432
Phones     1            1   37378260992
Phones     1            1   103442432

What I'd like it todo is sum the MEM NUM_VCPU and CAPACITY based on the name but also retain the NAME:

NAME      MEM     NUM_VCPU  CAPACITY
Core      13         4      263057293312
Phones    6          6      64562774016

UPDATE:

Based on the answers I adjusted the SQL code and this works like it should now:

select t1.NAME, t1.STORAGE, t2.CPU, t2.MEMORY
from 
(SELECT vapp.NAME [NAME]
     ,SUM(ceiling(convert(decimal(21, 0), hdd.CAPACITY)/(1024*1024*1024))) AS STORAGE
     FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
     INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
     ON memcpu.ID = hdd.VM_ID
     INNER JOIN [VCenterServer].[dbo].[VPXV_RESOURCE_POOL] AS vapp
     ON memcpu.RESOURCE_GROUP_ID = vapp.RESOURCEPOOLID
     GROUP BY vapp.NAME  
) t1
left join
(SELECT vapp.NAME [NAME]
     ,SUM(memcpu.[MEM_SIZE_MB]/1024) AS [MEMORY]
     ,SUM(memcpu.[NUM_VCPU]) AS [CPU]
     FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
     INNER JOIN [VCenterServer].[dbo].[VPXV_RESOURCE_POOL] AS vapp
     ON memcpu.RESOURCE_GROUP_ID = vapp.RESOURCEPOOLID
     GROUP BY vapp.NAME  
) t2
on
t1.NAME = t2.NAME
ORDER BY t1.NAME

Solution 1

You can simply remove unnecessary columns from the GROUP BY clause

  SELECT vapp.NAME
         ,SUM(memcpu.[MEM_SIZE_MB]/1000) AS [MEM_SIZE_MB]
         ,SUM(memcpu.[NUM_VCPU]) AS [NUM_VCPU]
         ,SUM(convert(bigint, hdd.CAPACITY)) AS CAPACITY
  FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
    INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
      ON memcpu.ID = hdd.VM_ID
    INNER JOIN [VCenterServer].[dbo].[VPXV_RESOURCE_POOL] AS vapp
      ON memcpu.RESOURCE_GROUP_ID = vapp.RESOURCEPOOLID
  GROUP BY vapp.NAME;

Solution 2

Please try:

SELECT
    Name,
    SUM(MEM) MEM,
    SUM(NUM_VCPU) NUM_VCPU,
    SUM(CAPACITY) CAPACITY
FROM(
    SELECT       
        vapp.NAME, 
        memcpu.[MEM_SIZE_MB]/1000 MEM, 
        memcpu.[NUM_VCPU] NUM_VCPU, 
        SUM(CONVERT(BIGINT, hdd.CAPACITY)) OVER(PARTITION BY
            memcpu.[ID], 
            memcpu.[DNS_NAME],
            memcpu.[MEM_SIZE_MB], 
            memcpu.[NUM_VCPU], 
            hdd.PATH, 
            hdd.CAPACITY, 
            vapp.RESOURCEPOOLID, 
            vapp.NAME) CAPACITY 

      FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
          INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
          ON memcpu.ID = hdd.VM_ID
          INNER JOIN [VCenterServer].[dbo].[VPXV_RESOURCE_POOL] AS vapp
          ON memcpu.RESOURCE_GROUP_ID = vapp.RESOURCEPOOLID
    )x
    GROUP BY NAME