MySQL - 在某些行上选择 AVG，在所有行上选择 SUM

Question

我试图对某些特定行的值取平均值，但对所有行的体积求和。

我有 2 个表，一个名为 exchanges，另一个名为 valid_country。

交流

+-----------+----------+--------------+----------+--------+
|     id    |   ref    |     country  |   value  | volume |
+-----------+----------+--------------+----------+--------+
|     1     |  1029    |      DE      |   1 000  |   100  |
+-----------+----------+--------------+----------+--------+
|     2     |  1029    |      US      |   2 000  |   250  |
+-----------+----------+--------------+----------+--------+
|     3     |  1029    |      FR      |   3 500  |   300  |
+-----------+----------+--------------+----------+--------+
|     4     |  1053    |      UK      |   1 200  |   110  |
+-----------+----------+--------------+----------+--------+
|     5     |  1029    |      RU      |     900  |    70  |
+-----------+----------+--------------+----------+--------+

此表包含许多引用文献 (ref)，它们具有不同的国家/地区，它们本身具有不同的值(value)和数量。

有效国家

+--------------+--------------+
|     ref      |   country    |
+--------------+--------------+
|    1029      |     US       |
+--------------+--------------+
|    1029      |     RU       |
+--------------+--------------+
|    1053      |     UK       |
+--------------+--------------+

此表列出了可以对其取平均值的所有“好”国家/地区。

我想要的查询结果是:

+----------+------------+-------------+
|   ref    | AVG(value) | SUM(volume) |
+----------+------------+-------------+
|  1029    |    1 450   |     720     |
+----------+------------+-------------+
|  1053    |    1 200   |     110     |
+----------+------------+-------------+

首先 ref 是 GROUP BY。Ref 1029 应AVERAGE 值仅为美国和 RU(因为表 valid_country)但 SUM 所有国家的体积。Ref 1053 也是如此，但由于只有一行，所以很容易。

这里有点Fiddle . SQL 请求是错误的，因为它计算了所有国家的平均值，而不仅仅是好的国家。

Answer 1

您可以使用 LEFT JOIN和 CASE忽略 AVG ( SQLFiddle ) 中某些值的语句:

SELECT e.ref,
       AVG(CASE WHEN vc.country IS NOT NULL THEN e.value END) AS average,
       SUM(e.volume) AS volume
FROM exchanges e
LEFT JOIN valid_country vc ON ( vc.country = e.country )
GROUP BY e.ref

CASE如果不匹配则返回 NULL，AVG 忽略这些值:

|  ref | average | volume |
|------|---------|--------|
| 1029 |    1450 |    720 |
| 1053 |    1200 |    110 |