# Combining cells and doing math

### Question

• I have a table that has a column with text in it.  I need to combine the text that has the same id, get the length of the text, and then perform some math.

```--Create/Populate [#ResultText]:
if object_id('tempdb..[#ResultText]','U') is not null drop table [#ResultText]
go
create table [#ResultText]
(
[resulttextid] int
,[resultid] int
,[resulttextorder] int
,[resulttexttext] varchar(max)
)
go
set nocount on
insert [#ResultText] select 1, 50, 1, 'the quick brown fox jumped over the lazy dogs.'
insert [#ResultText] select 2, 50, 2, 'Big dwarves heckle my top quiz of jinx.'
insert [#ResultText] select 3, 51, 1, 'Vamp fox held quartz duck just by wing.'
insert [#ResultText] select 4, 51, 2, 'and a few more'
insert [#ResultText] select 5, 52, 1, 'Five jumping wizards hex bolty quick.'
go
-- select * from [#ResultText]```

So what I want to do is take the ResultTextText for the rows that have 50 for the resultid and combine them.  I need to get the total length of the characters (45+38=83). Then I need to divide by 10, always rounding up. So I would get 8.3, with the file returned result as 9.

For 51, there are 53 characters, divided by 10 and round up and you get 6.

For 52, there are 37 characters, divided by 10 and round up and you get 4.

I am using C# to perform the query, so would it be better to perform all of this in SQL, or just I get the text result and use C# to do the math?

Friday, July 19, 2013 11:07 PM

• Try using CEILING function , if the rounding needed to be next possible integer value.

```select resultid,CEILING(sum(len(resulttexttext))/10.0) from #ResultText
group by resultid```

Saturday, July 20, 2013 6:49 AM

### All replies

• The below query will result as you want.

select resultid, sum(len(resulttexttext)) from  [#ResultText]

group by resultid

Friday, July 19, 2013 11:47 PM
• try this,

```select resultid, round(cast(resulttexttext as decimal)/10.0,0) resulttexttext from (
select resultid, sum(len(resulttexttext)) resulttexttext from  [#ResultText]
group by resultid
) x```

Regards, RSingh

Saturday, July 20, 2013 2:14 AM
• Try using CEILING function , if the rounding needed to be next possible integer value.

```select resultid,CEILING(sum(len(resulttexttext))/10.0) from #ResultText
group by resultid```