SQL Server : Sum by Row to output -

trying display sum of same rows output.

here sql code

select      replace(replace(replace(replace(dbo.identity_map.name,'my company\',''),'-vlan2',''),'.vlan2\',''),'.instr\','') site,     count (case                             when dbo.sem_agent.agent_version '11.%'                                 , dbo.sem_computer.operation_system 'windows%' 1            end) 'windows-sep-11',     count (case                             when dbo.sem_agent.agent_version '11.%'                                 , dbo.sem_computer.operation_system 'mac%' 1            end) 'mac-sep-11',     count(case                             when dbo.sem_agent.agent_version '12.%'                                 , dbo.sem_computer.operation_system 'windows%' 1           end) 'windows-sep-12',     count(case                             when dbo.sem_agent.agent_version '12.%'                                 , dbo.sem_computer.operation_system 'mac%' 1           end) 'mac-sep-12'        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       name '%31r%' group     dbo.identity_map.name order     site 

this output

however, output

and updated sql code adding sum() around count , grouping name , error

cannot perform aggregate function on expression containing aggregate or subquery.

code:

select      replace(replace(replace(replace(dbo.identity_map.name,'my company\',''),'-vlan2',''),'.vlan2\',''),'.instr\','') site,     sum(count (case                             when dbo.sem_agent.agent_version '11.%'                                 , dbo.sem_computer.operation_system 'windows%' 1                           end)) 'windows-sep-11',     sum(count (case                             when dbo.sem_agent.agent_version '11.%'                                 , dbo.sem_computer.operation_system 'mac%' 1                           end)) 'mac-sep-11',     sum(count(case                             when dbo.sem_agent.agent_version '12.%'                                 , dbo.sem_computer.operation_system 'windows%' 1                           end)) 'windows-sep-12',     sum(count(case                             when dbo.sem_agent.agent_version '12.%'                                 , dbo.sem_computer.operation_system 'mac%' 1                           end)) 'mac-sep-12'        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       name '%31r%' group      name 

thanks!

your group needs match want group by. since you're altering text "dbo.identity_map.name", need use group by.

select replace(replace(replace(replace(dbo.identity_map.name,'my company\',''),'-vlan2',''),'.vlan2\',''),'.instr\','') site,                    count (case                             when dbo.sem_agent.agent_version '11.%'                                 , dbo.sem_computer.operation_system 'windows%' 1                           end) 'windows-sep-11',                    count (case                             when dbo.sem_agent.agent_version '11.%'                                 , dbo.sem_computer.operation_system 'mac%' 1                           end) 'mac-sep-11',                    count(case                             when dbo.sem_agent.agent_version '12.%'                                 , dbo.sem_computer.operation_system 'windows%' 1                           end) 'windows-sep-12',                    count(case                             when dbo.sem_agent.agent_version '12.%'                                 , dbo.sem_computer.operation_system 'mac%' 1                           end) 'mac-sep-12'               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              name '%31r%'             group replace(replace(replace(replace(dbo.identity_map.name,'my company\',''),'-vlan2',''),'.vlan2\',''),'.instr\','')             order site