MYSQL查询均价

Question

我必须计算格罗宁根房屋的平均价格。尽管价格不是存储为数字，而是存储为字符串(带有一些附加信息)，并且它使用点(“.”)作为千位分隔符。价格在荷兰语中存储为“Vraagprijs”。

表格结果为:

€ 95.000 k.k.

€ 116.500 v.o.n.

€ 115.000 v.o.n.

如此往复...

我的查询:

'$'SELECT AVG(SUBSTRING(value,8,8)) AS AveragePrice_Groningen 
  FROM properties 
  WHERE name = 'vraagprijs' 
  AND EXISTS (SELECT * 
  FROM estate 
  WHERE pc_wp LIKE '%Groningen%' 
  AND properties.woid = estate.id); 

结果是:209.47509187620884但它必须是:

20947509187620,884

我怎样才能完成这个工作？

Answer 1

AVG(SUBSTRING(value,8,8)) 所做的工作:

示例

MariaDB [yourSchema]> SELECT *,SUBSTRING(`value`,8,8), SUBSTRING_INDEX(SUBSTRING_INDEX(`value`, ' ', -2),' ',1) FROM properties;
+----+-----------------------+------------------------+----------------------------------------------------------+
| id | value                 | SUBSTRING(`value`,8,8) | SUBSTRING_INDEX(SUBSTRING_INDEX(`value`, ' ', -2),' ',1) |
+----+-----------------------+------------------------+----------------------------------------------------------+
|  1 | € 95.000 k.k.    | 95.000 k               | 95.000                                                   |
|  2 | € 116.500 v.o.n. | 116.500                | 116.500                                                  |
|  3 | € 115.000 v.o.n. | 115.000                | 115.000                                                  |
+----+-----------------------+------------------------+----------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [yourSchema]>

**将其更改为**

AVG(SUBSTRING_INDEX(SUBSTRING_INDEX(`value`, ' ', -2),' ',1))