Conversion failed when converting the varchar value xxx to int

• mal kal

  SSC Enthusiast

  Points: 120

  More actions

  December 6, 2006 at 2:49 am

  #171210

  I am trying to have the aggregate value 1 replaced by 'only one' in my results.

  My data looks like this:

  Customer_id (int) ; Salesperson_id (int)

  1 ;                       1

  2 ;                       1

  3 ;                       1

  4 ;                       2

  5 ;                       2

  6 ;                       3

  ...this is the solution I tried:

  select

  case when count(convert(varchar(15), customer_id)) = 1

  Then 'only one customer'

  else count(convert(varchar(15), kund_id)) end as no_of_customers,

  salesperson_id

  from customerdb

  group by

  salesperson_id

  ...and this is the result I was hoping for

  no_of_customers ; salesperson_id

  3 ;                        1

  2;                         2

  only one customer;  3

  ...but SQL server only returns: Conversion failed when converting the varchar value ...

• ijaz

  SSCommitted

  Points: 1912

  More actions

  December 6, 2006 at 3:21 am

  #676156

  Try it.

  select

  case when count(customer_id) = 1

  Then 'only one customer'

  else Convert(varchar(15),count(kund_id)) end as no_of_customers,

  salesperson_id

  from customerdb

  group by

  salesperson_id

   

  Cheers

  cheers

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  December 6, 2006 at 8:51 am

  #676260

  What went wrong - an explanation.

  COUNT is an INTEGER value.

  count(convert(varchar(15), kund_id)) end as no_of_customers

  Since the first values returned are COUNTs, the column becomes INTEGER. Then you get a value that is not an INTEGER and it cannot be converted to an INTEGER. So you get the error.

  The solution is as Ijaz shows, you must convert the COUNT to VARCHAR, then the column becomes VARCHAR.

  -SQLBill

• mal kal

  SSC Enthusiast

  Points: 120

  More actions

  December 6, 2006 at 11:56 am

  #676367

  Thanks tons! Works fine.