How to get average of multiple variables-CodePudding

Table name: products,

column names: rice_price, sugar_price

I would like to get the average of both columns separately. For example;

SELECT
  AVG(rice_price) avg_rice,
  AVG(sugar_price) avg_sugar
FROM
  products

If I run this query on SQL server, I get the message below

Msg 8117, Level 16, State 1, Line 4 Operand data type nvarchar is invalid for avg operator.

What could be the solution?

CodePudding user response：

If most of them look like numbers, you could use this, which will exclude the ones that don't convert nicely, by handling them as null:

SELECT
  AVG(try_convert(numeric(18,4),rice_price)) avg_rice,
  AVG(try_convert(numeric(18,4),sugar_price)) avg_sugar
FROM
  products

But you should be changing your datatypes as has been pointed out in the comments. This kind of query will help you discover the ones that aren't good.

SELECT *
FROM products
WHERE rice_price IS NOT NULL
AND try_convert(numeric(18,4),rice_price) IS NULL

SELECT *
FROM products
WHERE sugar_price IS NOT NULL
AND try_convert(numeric(18,4),sugar_price) IS NULL

The IS_NUMERIC function can work for this too, but I find I have switched to using TRY_CONVERT in this situation, because it feels more flexible - I can use whatever data type I need.