September 29, 2009 at 9:23 am
I need to pull a number out of a string and sum the result:
select Convert(money,SubString(strrow,8,5) from tComments where strrow like '%Value:%'
I successfully get this to show:
10.95
9.95
10.95
11.95
But how do I sum these numbers? I tried wrapping SUM around the whole thing, then just SUM around the 'convert' part, but neither has worked.
September 29, 2009 at 9:29 am
Can you be a little more specific about what 'didn't work' means?
SELECT SUM(Convert(money,SubString(strrow,8,5)))
from tComments
where strrow like '%Value:%'
should work. If it doesn't, post the error/result.
September 29, 2009 at 9:46 am
That works, I left out one part, I have to sum the most recent 500, the query would look like this:
select TOP 500 Convert(money,SubString(strrow,8,5) from tComments where strrow like '%Value:%'
ORDER BY DateStamp DESC
I don't know where to put the SUM, I tried a few combinations but it didn't work.
September 29, 2009 at 9:52 am
;WITH Filter AS (
SELECT TOP 500 Convert(money,SubString(strrow,8,5) amount
FROM tComments
where strrow like '%Value:%'
ORDER BY DateStamp DESC)
SELECT SUM(Amount) Amount FROM Filter
Or if you prefer the 2000 version:
SELECT SUM(Amount) Amount
FROM (
SELECT TOP 500 Convert(money,SubString(strrow,8,5) Amount
FROM tComments
where strrow like '%Value:%'
ORDER BY DateStamp DESC) A
September 29, 2009 at 9:55 am
I think you need a subquery or a CTE.
I see Seth has posted what I'd use.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply