none
Sum of the data of Varchar column in t-sql

    Question

  • Hi All,

    I have one table with 3 columns..let say RecNo, TypeName, Amount and the data in my table looks like..

    RecNo  TypeName         Amount

      5            A                00091900

      6            B                00051500

      6            C                00055500

      6            D                00012300

      7          003             00211200  --> Sum(Amount)  and 003 is the count of RecNo=6

    The Amount column data type is varchar(8)

    I want to do the summation of the Amount column values, the total should be prefixed with zeros based on the total...let say

    If total = XX  then I want the result to be       -   0000XX00

    If total = XXX  then I want the result to be   -    000XXX00

    If total = XXXX  then I want the result to be   -    00XXXX00

    If total = XXXXX  then I want the result to be  -   0XXXXX00

    If total = XXXXXX  then I want the result to be  -  XXXXXX00

    So please I need t-Sql query to get the above output, its very urgent.

    Thanks in Advance,

    RH


    sql

    • Changed type Naomi NModerator Sunday, April 01, 2012 5:53 AM Question rather than discussion
    Saturday, March 31, 2012 9:06 PM

Answers

All replies

  • SELECT RIGHT('000000' + @x + '00', 8)


    N 56°04'39.26"
    E 12°55'05.63"

    Saturday, March 31, 2012 9:45 PM
  • It didn't work please anybody send me the t-sql query for it.

    THanks,

    RH


    sql

    Sunday, April 01, 2012 12:20 AM
  • create table #t (recno int,typename char(1),amount varchar(8))


    insert into #t values (5,'A','00091900')
    insert into #t values (6,'B','00051500')
    insert into #t values (6,'C','00055500')
    insert into #t values (6,'D','00012300')




    select *,case when len(s)=6 then 
               left('00' + cast(s as varchar(8)) , 8) end 
    from
    (
    select*,sum(cast(amount as int)) over ()s from #t
    ) as der

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by KJian_ Monday, April 09, 2012 2:28 AM
    Sunday, April 01, 2012 6:43 AM
    Answerer
  • Hi,

    In simple form use group by and union all.

    Sunday, April 01, 2012 8:38 AM
  • Friday, April 06, 2012 11:20 AM
    Moderator
  • Please try this. It will prefix with zero based on the value length. Suppose your value has 3 character and end with 2 zero then it will add 3 zero at left side.

    Create table #TempTable
    (
    RecNo int,
    TypeName varchar(50),
    Amount int
    )
    GO
    
    INSERT INTO #TempTable
    VALUES
    (5,'A',919),
    (6,'B',515),
    (6,'C',555),
    (6,'D',123),
    (7,'003',2112)
    Go
    
    
    SELECT RecNo,TypeName,Amount,
    RIGHT(REPLICATE('0',8) + (CAST(amount AS VARCHAR(8)) + '00'),8)  
    FROM #TempTable
    Go


    Friday, April 06, 2012 12:20 PM
  • Try

    declare @t table(recno int,typename char(1),amount varchar(8)) insert into @t values (5,'A','00091900') , (6,'B','00051500') , (6,'C','00055500') , (6,'D','00012300') select 7 ,right(cast(1000 + count (case recno when 6 then 1 end) as varchar(max)),3) ,right(cast(1000000000 + sum(cast(amount as int)) as varchar(max)),9)

    from @t



    Serg



    • Edited by SergNL Friday, April 06, 2012 1:05 PM
    Friday, April 06, 2012 1:02 PM