mysql - SQL 如何使用收集的 SUM 值计算行值

Question

mlb_pitchers 表

+------+-----------+-----------+-----+-----+
| year | playerID  | nameLast  | IP  | ER  |
+------+-----------+-----------+-----+-----+
| 1903 | mathech01 | Mathewson | 366 |  92 |
| 1903 | youngcy01 | Young     | 341 |  79 |
| 1904 | mathech01 | Mathewson | 367 |  83 |
| 1904 | walshed01 | Walsh     | 110 |  32 |
| 1904 | youngcy01 | Young     | 380 |  83 |
| 1905 | mathech01 | Mathewson | 338 |  48 |
| 1905 | walshed01 | Walsh     | 136 |  33 |
| 1905 | youngcy01 | Young     | 320 |  65 |
| 1906 | mathech01 | Mathewson | 266 |  88 |
| 1906 | walshed01 | Walsh     | 278 |  58 |
| 1906 | youngcy01 | Young     | 287 | 102 |
+------+-----------+-----------+-----+-----+

首先是获得从 1903 年到 1906 年投球但也必须在 1903 年投球的每个投手的 IP 和 ER 的总和。

SELECT
  playerID,
  nameLast,
  SUM(IP),
  SUM(ER)
FROM mlb_pitchers
WHERE year >= 1903
AND year <= 1906
GROUP BY playerID,
         nameLast
HAVING SUM(CASE
  WHEN year = 1903 THEN 1
  ELSE 0
END) > 0

输出

+-----------+-----------+---------+---------+
| playerID  | nameLast  | SUM(IP) | SUM(ER) |
+-----------+-----------+---------+---------+
| mathech01 | Mathewson |    1337 |     311 |
| youngcy01 | Young     |    1328 |     329 |
+-----------+-----------+---------+---------+

我想做的是添加一个使用 SUM 值来计算投手的 ERA 的输出列(新行值)，但我不知道要向 SQL 添加什么。 ERA = ER/IP * 9。所以输出应该是这样的:

+-----------+-----------+---------+---------+------+
| playerID  | nameLast  | SUM(IP) | SUM(ER) | ERA  |
+-----------+-----------+---------+---------+------+
| mathech01 | Mathewson |    1337 |     311 | 2.09 |
| youngcy01 | Young     |    1328 |     329 | 2.23 |
+-----------+-----------+---------+---------+------+

Answer 1

添加一个额外的输出列来计算 ERA，如下所示:

SELECT playerID, nameLast, SUM(IP), SUM(ER),  (SUM(ER) / SUM(IP)) * 9.0 as ERA
FROM mlb_pitchers 
WHERE year >= 1903 AND year <= 1906 
GROUP BY playerID, nameLast 
HAVING SUM(CASE WHEN year = 1903 THEN 1 ELSE 0 END) > 0