Sum on nvarhcar column

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  March 19, 2009 at 9:20 am

  #132161

  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.

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  March 19, 2009 at 9:35 am

  #962819

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

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  March 19, 2009 at 9:37 am

  #962823

  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]