Sum of Column of Blob type

[Connie]

I have a column in my table that is an Image (blob). The data stored
in this blob is basically pdf files. I need a query to determine the
total size of these blob's in the database. Here is what is
happening, if I run it as below I get 23736000 for a result.

select sum(datalength (cast(document AS binary(8000)))) from
plan_report

If I run it like this select sum(datalength (cast(document AS
varbinary(8000) ))) from plan_report
I get 23736000 also as a result

If I run it like this select sum(datalength (cast(document AS
varbinary))) from plan_report
I get 89010 this as a result

If I run this: select datalength(docu ment) from plan_report then I
get a result for each row and when I sum those rows I get 5584452854
this as a result.

I do not believe the first 3 queries are returning correct result sets
due to the limit on the fields (binary/varbinary) and since this is an
image field I cannot convert to anything else.....

I am out of ideas, and any help you can give me will be greatly
appreciated...

[Plamen Ratchev]

Re: Sum of Column of Blob type

You do not have to convert your BLOB column. DATALENGTH will return the
correct size of the BLOB column if you use it directly.

Something like this:

SELECT SUM(COALESCE(DA TALENGTH(docume nt), 0))
FROM plan_report

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Erland Sommarskog]

Re: Sum of Column of Blob type

Connie (csawyer@rwbair d.com) writes:

If I run it like this select sum(datalength (cast(document AS
varbinary))) from plan_report
I get 89010 this as a result

Because in this context the default is varbinary(30).

If I run this: select datalength(docu ment) from plan_report then I
get a result for each row and when I sum those rows I get 5584452854
this as a result.

The obvious query would be

SELECT SUM(datalength( document)) FROM plan_report

you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:

SELECT SUM(cast(datale ngth(document) as bigint)) FROM plan_report


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Plamen Ratchev]

Re: Sum of Column of Blob type

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns990F2D6 DACA2Yazorman@1 27.0.0.1...

>
you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:
>
SELECT SUM(cast(datale ngth(document) as bigint)) FROM plan_report
>

Good catch Erland! I didn't see the big number and the overflow...

Plamen Ratchev

http://www.SQLStudio.com

[Connie]

Re: Sum of Column of Blob type

On Apr 10, 4:46 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

"Erland Sommarskog" <esq...@sommars kog.sewrote in message
>
news:Xns990F2D6 DACA2Yazorman@1 27.0.0.1...
>
>
>

you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:

>

SELECT SUM(cast(datale ngth(document) as bigint)) FROM plan_report

>
Good catch Erland! I didn't see the big number and the overflow...
>
Plamen Ratchevhttp://www.SQLStudio.c om

Thanks everyone....... your all awesome, Yes Erland I did try the
first option, but I did not try with the bigint.....I was looking at
it too long and getting frustrated I guess, but thanks again everyone,