Mysql - 选择同一列平均值的差异

Question

您好，我正在尝试找出同一列中多个值的平均值之间的差异。

这是架构。

CREATE TABLE `product_priceDiff` (
  `mark` char(15) NOT NULL,
  `markId` int(11) NOT NULL,
  `found_date` date DEFAULT '0000-00-00',
  `found_price` decimal(15,3) DEFAULT NULL,
  `confirmation_date` date DEFAULT '0000-00-00',
  `confirmed_price` decimal(15,3) DEFAULT NULL,
  `price_difference` decimal(15,3) DEFAULT NULL,
  `action` char(30) DEFAULT NULL,
  PRIMARY KEY (`mark`,`markId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

这里是一些示例值

+------------------+----------+------------+-------------+-------------------+-----------------+------------------+--------+
| mark             | markId   | found_date | found_price | confirmation_date | confirmed_price | price_difference | action |
+------------------+----------+------------+-------------+-------------------+-----------------+------------------+--------+
| soap             |        6 | 2014-01-13 |       0.410 | 2014-01-15        |           0.420 |            2.439 | BUY    |
| lotion           |        7 | 2013-09-13 |       0.000 | 2013-09-13        |           0.170 |            0.000 | BUY    |
| shaving_cream    |        8 | 2014-01-09 |      41.500 | 2014-01-10        |          42.000 |            1.205 | BUY    |    | 
| hairgel          |       19 | 2014-01-13 |       8.220 | 2014-01-16        |           8.190 |           -0.365 | SELL   |
| aftershaves      |       20 | 2011-07-12 |       0.000 | 2011-07-12        |           7.500 |            0.000 | SELL   |
| shampoo          |       21 | 2014-01-14 |      46.870 | 2014-01-17        |          46.480 |           -0.832 | SELL   |
+------------------+----------+------------+-------------+-------------------+-----------------+------------------+--------+

我想找到的是平均价格差异。公式为

Average(price_difference) @ BUY - Average(price_difference) @ SELL

我尝试使用此查询来执行此操作，但它根本不起作用

SELECT 
    AVG(CASE WHEN `action` = "BUY" THEN `price_difference`) 
    - AVG(CASE WHEN `action` = "SELL" THEN `price_difference`) 
    FROM `product_priceDiff`;

这给了我一个错误

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'action = "BUY" then price_difference) - avg(case when action= "SELL" then price' at line 1

请提供有关我应该做什么才能获得所需值的任何建议。谢谢，提前

麦克斯

Answer 1

记录:

问题是 CASE statement 的“语法” 。

CASE WHEN <condition> THEN <action> END

您错过了END