jwabsolution1 jwabsolution1 - 28 days ago 5
SQL Question

SQL - adding column A depending on value in column B

I'm trying to add the values in a column based on the value in another. But the output is not what I am expecting. Any help tweeking this query would be helpful.
Example:

SELECT [custNo]
,[cityNo]
,sum([TransactionCount]) as transcount
FROM [log].[dbo].[TransactionSummary]
where [format] = 23
or [format] = 25
or [format] = 38
or [format] >=400 and [format] <= 499
or [format] >=800 and [format] <= 899
and transactiondate = '6/21/16'
group by custno, cityno


Will result in an output such as:

custno cityno transcount
1 10220 4
1 10277 6
501 10284 9
501 10284 17
604 10248 4
604 10248 317


What I'd like to have as the output is more like:

custno cityno transcount
1 10220 4
1 10277 6
501 10284 26 (sum of 17+9)
604 10248 321 (sum of 317+4)

Answer
  1. Store your distinct data in table variable..

    declare @Table Table(ID int identity, custNo int, cityNo int, Other varchar(500))
    
    insert into @Table(custNo, cityNo)
       SELECT distinct 
           [custNo], [cityNo]
       FROM 
           [log].[dbo].[TransactionSummary]
       WHERE
           [format] = 23
           or [format] = 25
           or [format] = 38
           or [format] >= 400 and [format] <= 499
           or [format] >= 800 and [format] <= 899
           and transactiondate = '6/21/16'
    
  2. Use while loop to store your description in Other column using COALESCE function

    declare @i int, @cnt int, @custNo  int
    declare @Result VARCHAR(Max) 
    
    select @cnt = count(*), @i = 1 
    from @Table
    
    while @i <= @cnt
    begin
        select @custNo = custNo 
        from @Table 
        where ID = @i
    
       (if select count(*)  FROM TransactionSummary where custNo =@custNo )>1
    

    begin set @Result ='' SELECT @Result = COALESCE(@Result + '+', '') + cast(TransactionCount as varchar(50)) FROM TransactionSummary where custNo =@custNo update @Table set Other ='(sum of '+@Result + ')' where id=@id end set @i=@i+1 end

  3. Finally write your query..

    SELECT [tr.custNo] ,[tr.cityNo] ,cast(sum([tr.TransactionCount]) as varchar(50)) + isnull(Other,'' ) transcount FROM [log].[dbo].[TransactionSummary] tr left outer join @Table t on tr.custNo=t.custNo and tr.cityNo=t.cityNo where [format] = 23 or [format] = 25 or [format] = 38 or [format] >=400 and [format] <= 499 or [format] >=800 and [format] <= 899 and transactiondate = '6/21/16' group by custno, cityno

Comments