Give this a try. I think you need to partition the data by computer name, and run SUM() on that.
use CM_PS1 select distinct sys.name0 as 'Machine Name', hrd.SerialNumber0, COUNT (cpu.AddressWidth0) as 'Number of CPU', SUM(cpu.NumberOfCores0) OVER (PARTITION BY sys.Name0) as 'Number of Cores', sft.SoftwareName0 as 'App Name' , sft.edition0 as 'Edition', sft.MajorVersion0 as 'Major Version', sft.MinorVersion0 as 'Minor Version', CASE WHEN hrd.IsVirtual0 IS NULL then 'No' Else hrd.IsVirtual0 End as 'Is Virtual' from v_R_System sys INNER JOIN v_GS_BDNA_BDNA_SOFTWARE_DATA_1_30 sft on sft.SMSUniqueIdentifier0 = sys.SMS_Unique_Identifier0 inner join v_GS_BDNA_BDNA_HARDWARE_DATA_1_30 hrd on hrd.SMSUniqueIdentifier0 = sys.SMS_Unique_Identifier0 INNER JOIN v_GS_Processor cpu on cpu.ResourceID = sys.ResourceID where sft.SoftwareName0 = 'SQL Server' and sft.Component0='Database Engine' group by sys.name0, hrd.SerialNumber0, cpu.NumberOfCores0, sft.SoftwareName0, sft.edition0, sft.MajorVersion0, sft.MinorVersion0, hrd.IsVirtual0 Cheers, Trevor Sullivan Microsoft PowerShell MVP From: [email protected] [mailto:[email protected]] On Behalf Of Marcum, John Sent: Monday, June 2, 2014 7:55 AM To: SMS List ([email protected]) Subject: [mssms] SUM Data in TSQL I have a report that shows me the count of CPU's in my SQL servers. It also should show me a count of the cores but it doesn't. The reason why is I'm counting the CPU's but each row that contains a CPU also contains I number of cores. I need to SUM the number of cores from each row and total that into the single row that is returned in my count of CPU's. How would I SUM the data from those rows? use CM_PS1 select distinct sys.name0 as 'Machine Name', hrd.SerialNumber0, COUNT (cpu.AddressWidth0) as 'Number of CPU', cpu.NumberOfCores0 'Number of Cores', --I need to add all of these together sft.SoftwareName0 as 'App Name' , sft.edition0 as 'Edition', sft.MajorVersion0 as 'Major Version', sft.MinorVersion0 as 'Minor Version', CASE WHEN hrd.IsVirtual0 IS NULL then 'No' Else hrd.IsVirtual0 End as 'Is Virtual' from v_R_System sys INNER JOIN v_GS_BDNA_BDNA_SOFTWARE_DATA_1_30 sft on sft.SMSUniqueIdentifier0 = sys.SMS_Unique_Identifier0 inner join v_GS_BDNA_BDNA_HARDWARE_DATA_1_30 hrd on hrd.SMSUniqueIdentifier0 = sys.SMS_Unique_Identifier0 INNER JOIN v_GS_Processor cpu on cpu.ResourceID = sys.ResourceID where sft.SoftwareName0 = 'SQL Server' and sft.Component0='Database Engine' group by sys.name0, hrd.SerialNumber0, cpu.NumberOfCores0, sft.SoftwareName0, sft.edition0, sft.MajorVersion0, sft.MinorVersion0, hrd.IsVirtual0 _____ John Marcum MCITP, MCTS, MCSA Sr. Desktop Architect Bradley Arant Boult Cummings LLP _____ _____ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
