Sum on nvarhcar column

  • I am trying to insert result of exec master.dbo.xp_restore_filelistonly into a temp table. The result has 140 files(mdf,ldf,ndf), Now I wan to SUM the size of ndf and ldf files and get total sise of data nad log file.

    Here is the result

    RowID LogicalName PhysicaName Type FileGrpName Size Maxsize

    122Rev_logE:\SQLDATA_LOG\Rev_log.ldfLNULL626235801635184372080640

    121Rev_Data E:\SQLDATA_DATA\Rev.mdfDPRIMARY10800332835184372080640

    select sum([Size]) from #T where type='d'

    select sum() from #T where type='L'

    I get the following error

    Msg 8117, Level 16, State 1, Line 29

    Operand data type varchar is invalid for sum operator.

  • change the type of your size column to bigint or cast(size as bigint) on the column.

  • Cast it first, then sum

    select sum(CAST [Size] AS DECIMAL(18,0)) from #T where type='d'

    select sum(CAST AS DECIMAL(18,0)) from #T where type='L'

    That should do it

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply