invalid column name sum RSS

• • Reply
  Capricon Use...

  Member

  239 Points

  1074 Posts

  invalid column name sum

  Jan 29, 2019 09:24 AM|Capricon User|LINK

  i treid this query 

  select sum[amount ist] as Amount , id, category from abctable 

  where category in [ 'a', 'b', 'c' ] 

  group by id , category 

  now this an error 

  invalid column name SUM 

  what should  i do 

• • Reply
  wmec

  Contributor

  6446 Points

  5738 Posts

  Re: invalid column name sum

  Jan 29, 2019 10:01 AM|wmec|LINK

  Try

  select sum([amount ist]) as Amount , id, category from abctable 
  where category in('a', 'b', 'c') 
  group by id , category 

  Many Thanks & Best Regards,
  Ai Guo
  
• • Reply
  Capricon Use...

  Member

  239 Points

  1074 Posts

  Re: invalid column name sum

  Mar 12, 2019 06:31 AM|Capricon User|LINK

  wmec

  select sum([amount ist]) as Amount , id, category from abctable
  where category in('a', 'b', 'c')
  group by id , category

  i do this .. this shows an error 

  "operand data type varchar is invalid for sum operator"

• • Reply
  limno

  All-Star

  123178 Points

  9995 Posts

  Moderator

  Re: invalid column name sum

  Mar 12, 2019 04:57 PM|limno|LINK

  select sum(try_cast([amount ist] as decimal(10,2)) )as Amount , id, category 
  from abctable 
  where category in ( 'a', 'b', 'c' ) 
  group by id , category 

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  PatriceSc

  All-Star

  47080 Points

  17333 Posts

  Re: invalid column name sum

  Mar 12, 2019 05:11 PM|PatriceSc|LINK

  Hi,

  Or consider changing your column type from varchar to decimal(10,2) or whatever is wide enough for you. Using  a varchar column to store numeric values seems a poor design. If you have a currency symbol, the currency information should be likely stored in its own column (so that you can handle conversion when doing your sum for example).