SQL sum function with inner join and a condition

Question

I have following query which produces bellow table:

  SELECT 
    memcpu.[ID]
  , memcpu.[DNS_NAME]
  , memcpu.[MEM_SIZE_MB] 
  , hdd.PATH
  , hdd.CAPACITY 
  FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
  INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
  ON memcpu.ID = hdd.VM_ID
  GROUP BY 
    memcpu.[ID]
  , memcpu.[DNS_NAME] 
  , memcpu.[MEM_SIZE_MB]
  , hdd.PATH
  , hdd.CAPACITY
  ;

TABLE:

ID  DNS_NAME    MEM_SIZE_MB PATH    CAPACITY
27  name27      2048        C:\     42947571712
28  name28      4096        C:\     42947571712
28  name28      4096        E:\     134214578176
37  name37      6144        C:\     42947571712
38  name38      2048        C:\     42947571712
39  name39      8192        C:\     145077825536

I'd like to find out how to do SUM on ID with a condition of all the same PATH variables. Basically sum up storage for each virtual machine so I can see Memory and total storage provisioned.

Desired TABLE (notice the ID 28, it has the C: and E: summed up):

Problems is that CAPACITY is defined as nvarchar(21)

ID  DNS_NAME    MEM_SIZE_MB     CAPACITY
27  name27      2048            42947571712
28  name28      4096            177162149888
37  name37      6144            42947571712
38  name38      2048            42947571712
39  name39      8192            145077825536

Answer 1

Remove the PATH and CAPACITY fields from the GROUP BY clause and sum CAPACITY in the SELECT.

Something like this should do:

SELECT 
  memcpu.[ID]
, memcpu.[DNS_NAME]
, memcpu.[MEM_SIZE_MB] 
, sum(convert(decimal(21, 0), hdd.CAPACITY))
FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
ON memcpu.ID = hdd.VM_ID
GROUP BY 
  memcpu.[ID]
, memcpu.[DNS_NAME] 
, memcpu.[MEM_SIZE_MB]
;

Answer 2

SELECT 
  memcpu.[ID]
 , memcpu.[DNS_NAME]
 , memcpu.[MEM_SIZE_MB] 

 , sum(hdd.CAPACITY ) CAPACITY 
FROM [VCenterServer].[dbo].[VPX_VM] AS memcpu
INNER JOIN [VCenterServer].[dbo].[VPX_GUEST_DISK] AS hdd
ON memcpu.ID = hdd.VM_ID
GROUP BY 
 memcpu.[ID]
 , memcpu.[DNS_NAME] 
 , memcpu.[MEM_SIZE_MB] ;