trying to convert avg to a decimal, syntax error

Question

I am trying to turn my avg value in to a decimal, but am getting a syntax error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INT, avg(item_price)) as total_price, count(basket_item) a' at line 4 in

my sql:

SELECT 
      item_id, 
      item_name, 
      CONVERT(INT, avg(item_price)) as total_price, 
      count(basket_item) as quantity, 
      item_price 
FROM basket b, items i
WHERE b.basket_user = ? 
AND i.item_id = b.basket_item  
AND i.item_quantity > 0

I have tried cast too but am getting the same error, I am using xampp php 7 version

@BilasSarker You too, it doesn't make sense to convert an integer to an integer. — sagi, Jun 19 '16 at 15:10
It doesn't matter, `AVG()` only works with numeric values, and return a numeric value. I suspect he meant to convert the `value` it self, not the `AVG(value)` @BilasSarker — sagi, Jun 19 '16 at 15:14
Have look on the link may be you can get your solution: http://stackoverflow.com/questions/11830509/cast-to-decimal-in-mysql — Bilas Sarker, Jun 19 '16 at 15:23

score 0 · Answer 1 · edited May 23 '17 at 12:25

avg is returning a DECIMAL. The code below should fix the problem.

More information on casting

SELECT CAST(2.234 AS UNSIGNED) FROM DUAL;

SELECT 
      item_id, 
      item_name, 
      CAST(avg(item_price) AS UNSIGNED) as total_price, 
      count(basket_item) as quantity, 
      item_price 
FROM basket b, items i
WHERE b.basket_user = ? 
AND i.item_id = b.basket_item  
AND i.item_quantity > 0

trying to convert avg to a decimal, syntax error

1 Answers1