none
adding rows by using sum function RRS feed

  • Question

  • i want to get addition of rows which have combined numeric and char values in column

    for example this one

    "25 emails sent"
    "3 sms sent"
    "12 sms sent"
    "22 emails sent"
    "71 emails sent"
    "70 sms sent"
    "175 sms sent"
    "5 emails sent"
    "12 emails sent"

    i want  to add numeric values in every row

    row datatype is varchar


    Thursday, August 29, 2019 5:52 PM

All replies

  • Hi manikanta6047,

    Hope the below code can help you!

    CREATE TABLE #NOTES(
    [Note_id] [int] IDENTITY(1,1) NOT NULL,
    [Notes] [varchar](30) NOT NULL,
    PRIMARY KEY ([Note_id])
    );
    
    INSERT INTO #NOTES ([Notes]) VALUES  
    ('25 emails sent'),
    ('3 sms sent'),
    ('12 sms sent'),
    ('22 emails sent'),
    ('71 emails sent'),
    ('70 sms sent'),
    ('175 sms sent'),
    ('5 emails sent'),
    ('12 emails sent');
    
    select [Notes],case when [Notes] like '%emails sent%' then SUBSTRING([Notes],0,CHARINDEX(' emails sent',[Notes],0)) else 0 end as [emails sent],
    case when [Notes] like '%sms sent%' then SUBSTRING([Notes],0,CHARINDEX(' sms sent',[Notes],0)) else 0 end as [sms sent]
    from #NOTES;
    
    select sum(case when [Notes] like '%emails sent%' then SUBSTRING([Notes],0,CHARINDEX(' emails sent',[Notes],0)) else 0 end) as [total emails sent],
    sum(case when [Notes] like '%sms sent%' then SUBSTRING([Notes],0,CHARINDEX(' sms sent',[Notes],0)) else 0 end) as [total sms sent],
    sum((case when [Notes] like '%emails sent%' then SUBSTRING([Notes],0,CHARINDEX(' emails sent',[Notes],0)) else 0 end)+
    ((case when [Notes] like '%sms sent%' then SUBSTRING([Notes],0,CHARINDEX(' sms sent',[Notes],0)) else 0 end))) as [total]
    from #NOTES;
    
     drop table #NOTES;
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    • Edited by Arulmouzhi Thursday, August 29, 2019 7:25 PM Added the total column
    • Proposed as answer by Shah EBanned Thursday, August 29, 2019 7:39 PM
    Thursday, August 29, 2019 6:19 PM
  • DECLARE @T TABLE (
    	Col varchar(50)
    );
    INSERT INTO @T VALUES 
    ('25 emails sent'),
    ('3 sms sent'),
    ('12 sms sent'),
    ('22 emails sent'),
    ('71 emails sent'),
    ('70 sms sent'),
    ('175 sms sent'),
    ('5 emails sent'),
    ('12 emails sent');
    
    SELECT SUM(CAST(LEFT(Col, CHARINDEX(' ', Col) - 1) AS int))
    FROM @T;


    A Fan of SSIS, SSRS and SSAS

    • Proposed as answer by Shah EBanned Thursday, August 29, 2019 7:39 PM
    Thursday, August 29, 2019 6:27 PM