sql server 2008 - SQL Query - Select with a total column -

i have query returns rows current , non-current items. current items have no value non-current items have value

i need query return current values , total value of non-current items:

sample:

name   status       value kb     current      0 kb     non-current  5 kb     current      0 kb     non-current  5 kb     non-current  5 kb     current      0 kb     current      0 kb     current      0     non-current  5     current      0     non-current  5     current      0     current      0 

i need:

name   status       value  totalvalue kb     current      0      15 kb     current      0      15 kb     current      0      15 kb     current      0      15 kb     current      0      15     current      0      10     current      0      10     current      0      10 

i've tried

select name,status,value,(select sum(value) table 

but totals values rather per name, if try group name error returns more 1 value.

this totals values rather per name, if try group name

you can use sum over clause:

select name, status, value,        sumpername = sum(value) on (partition name) table 

demo

(your sample data bad since names same)

if want partition status(which makes sample data better) have replace partition name partition status.

if want records status='current' have apply where:

select name, status, value,        sumpername = sum(value) on (partition name) table status = 'current' 

edit: use cte , apply where in outer query:

with cte (   select name, status, value,          sumpername = sum(value) on (partition name),          total = (select sum(value) table1)   table1 ) select * cte status = 'current' 

new demo (with sample data contains 2 different names)