Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Unanswered: Using AVG on a nvarchar field

    I have a question.... I have this database that I have to link to so I don't have control over datatypes but I need to take MIN, MAX, and AVG of a column - its a sample recording level field so items are numeric but again, stored in nvarchar. Point being, I can do MAX and MIN easily but AVG doesn't work due to datatype conflicts. Anyone know a way around this?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can use cast or convert. What type of numeric is stored in the nvarchar ?

  3. #3
    Join Date
    Oct 2003
    Posts
    16
    Originally posted by rnealejr
    You can use cast or convert. What type of numeric is stored in the nvarchar ?
    It will be a decimal number - i.e., .000412 or 343.52234 or something like that.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Try:

    select avg(cast(field as decimal(20,10))) from table

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    select avg(cast(Column as decimal(20,10))) from table
    WHERE ISNUMERIC(Column)=1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •