SQL Server : Sum by Row to output

Question

Trying to display sum of same rows as output.

Here is SQL code

SELECT 
    replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','') as Site,
    Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
           END) AS 'Windows-SEP-11',
    Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
           END) AS 'Mac-SEP-11',
    Count(CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
          END) AS 'Windows-SEP-12',
    Count(CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
          END) AS 'Mac-SEP-12'
FROM   
    dbo.sem_computer
INNER JOIN 
    [dbo].[V_SEM_COMPUTER] ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
INNER JOIN 
    dbo.SEM_AGENT ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
INNER JOIN 
    dbo.SEM_CLIENT ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
INNER JOIN 
    dbo.IDENTITY_MAP ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
INNER JOIN 
    dbo.PATTERN ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
WHERE  
    Name LIKE '%31r%'
GROUP BY
    dbo.IDENTITY_MAP.Name
ORDER BY
    Site

This is output

However, I would like the output to look like

And I updated the SQL code by adding SUM() around Count and grouping by Name and I get error

Cannot perform an aggregate function on an expression containing an aggregate or subquery.

Code:

SELECT 
    replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','') as Site,
    SUM(Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
                          END)) AS 'Windows-SEP-11',
    SUM(Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
                          END)) AS 'Mac-SEP-11',
    SUM(Count(CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
                          END)) AS 'Windows-SEP-12',
    SUM(Count(CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
                          END)) AS 'Mac-SEP-12'
FROM   
    dbo.sem_computer
INNER JOIN 
    [dbo].[V_SEM_COMPUTER] ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
INNER JOIN 
    dbo.SEM_AGENT ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
INNER JOIN 
    dbo.SEM_CLIENT ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
INNER JOIN 
    dbo.IDENTITY_MAP ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
INNER JOIN 
    dbo.PATTERN ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
WHERE  
    Name LIKE '%31r%'
GROUP BY 
    Name

Thanks!

Answer 1

Your group by needs to match exactly what you want to group it by. Since you're altering the text for "dbo.IDENTITY_MAP.Name", you need to use that as the group by.

SELECT replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','') as Site,
                   Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
                          END) AS 'Windows-SEP-11',
                   Count (CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
                          END) AS 'Mac-SEP-11',
                   Count(CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
                          END) AS 'Windows-SEP-12',
                   Count(CASE
                            WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                                AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
                          END) AS 'Mac-SEP-12'
            FROM   dbo.sem_computer
                   INNER JOIN [dbo].[V_SEM_COMPUTER]
                           ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                   INNER JOIN dbo.SEM_AGENT
                           ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                   INNER JOIN dbo.SEM_CLIENT
                           ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                   INNER JOIN dbo.IDENTITY_MAP
                           ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                   INNER JOIN dbo.PATTERN
                           ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
            WHERE  Name LIKE '%31r%'
            Group by replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','')
            Order by Site