Joining Summed data that has nulls - SQL Server -

how retain acct_name field appropriate when summing data below amount column , grouping line_num field? "null" values in line_num column cause problem in grouping terms when account name added. accounts c , d both have null values in line_num. if add acct_name group by clause, lose ability sum values line_num field.

i attempting sum lines of accounting , group based on line number. null data isn't doing, unfortunately it's data set handed.

original data:

acct_name   id      line_num   amount acct      1       1_01       100.0000 acct      1       1_01       -50.0000 acct      1       1_02       75.0000 acct      1       _02        125.0000 acct b      2       2_01       200.0000 acct b      2       2_01       50.0000 acct b      2       2_02       25.0000 acct c      3       3_01       75.0000 acct c      3       3_02       50.0000 acct c      3       3_03       -25.0000 acct c      3       null       65.0000 acct d      4       null       300.0000 acct d      4       _02        100.0000 acct d      4       null       -50.0000 acct d      4       null       75.0000 

if line_num value null, line allowed aggregated other null values. show in reports being unaccounted , can dealt appropriately.

ideal processed data set:

amount  line_num acct_name 390.00  null     null 225.00  _02      null 50.00   1_01     acct 75.00   1_02     acct 250.00  2_01     acct b 25.00   2_02     acct b 75.00   3_01     acct c 50.00   3_02     acct c -25.00  3_03     acct c 

here following queries have used:

select sum(amount), line_num dbo.tblrawdata group line_num 

this query works fine, not include account name in of aggregated fields. need account name in fields did not contain null values.

select sum(amount), line_num, acct_name dbo.tblrawdata group line_num, acct_name 

this query includes account name, ends grouping based on account name , not line_num.

select * dbo.tblrawdata inner join dbo.tblrawdata b on (a.line_num = b.line_num) (select sum(cast(amount money)) amount, line_num dbo.tblrawdata group line_num) 

this inner join intended join lines equivalent on line num, receiving cartesian result set. have not written join correctly or using incorrect command.

here query can used build same schema using:

create table [dbo].[tblrawdata]( [acct_name] [nvarchar](50) null, [id] [nvarchar](50) null, [line_num] [nvarchar] (50), [amount] [money] ) on [primary]  go  insert dbo.tblrawdata values ('acct a', '1', '1_01', '100') insert dbo.tblrawdata values ('acct a', '1', '1_01', '-50') insert dbo.tblrawdata values ('acct a', '1', '1_02', '75') insert dbo.tblrawdata values ('acct a', '1', '_02', '125') insert dbo.tblrawdata values ('acct b', '2', '2_01', '200') insert dbo.tblrawdata values ('acct b', '2', '2_01', '50') insert dbo.tblrawdata values ('acct b', '2', '2_02', '25') insert dbo.tblrawdata values ('acct c', '3', '3_01', '75') insert dbo.tblrawdata values ('acct c', '3', '3_02', '50') insert dbo.tblrawdata values ('acct c', '3', '3_03', '-25') insert dbo.tblrawdata values ('acct c', '3', '', '65') insert dbo.tblrawdata values ('acct d', '4', '', '300') insert dbo.tblrawdata values ('acct d', '4', '_02', '100') insert dbo.tblrawdata values ('acct d', '4', '', '-50') insert dbo.tblrawdata values ('acct d', '4', '', '75') 

p.s. sql fiddle appears inaccessible @ moment (might on end, don't know)

edit

take @ following code , holler if seems there blatant flaws in trying accomplish goal. i'd prefer acct_name remain null if line_item doesn't match up, perhaps can sort out.

if (select object_id('tempdb..#temp4')) not null begin     drop table #temp4 end  select sum(cast(amount money)) amount, line_num #temp4 dbo.tblrawdata group line_num  select * #temp4  select max(a.acct_name) acct_name, max(b.line_num) line_num, max(b.amount) amount dbo.tblrawdata inner join #temp4 b on (a.line_num = b.line_num) group b.line_num 

results:

acct_name  line_num amount acct d     null     390.00 acct d     _02      225.00 acct     1_01     50.00 acct     1_02     75.00 acct b     2_01     250.00 acct b     2_02     25.00 acct c     3_01     75.00 acct c     3_02     50.00 acct c     3_03     -25.00 

here go:

;with cte (     select  line_num,              sum(amount) amount,             min(acct_name) minacct_name,             max(acct_name) maxacct_name     tblrawdata     group line_num ) select  amount,         line_num,         case when line_num null          or minacct_name <> maxacct_name null         else minacct_name end acct_name cte