SQL - 如何对 GROUP BY 记录集求和？

Question

我不太擅长 SQL，需要一些帮助来弄清楚如何计算记录集中返回的数字。

这是到目前为止我对查询的了解:

SELECT DATEDIFF(m, originalretaildate, getdate()) AS age_in_months, count(email) AS number_of_valid_emails
FROM #result2
WHERE originalretaildate BETWEEN '20140415' AND GETDATE()
GROUP BY DATEDIFF(m, originalretaildate, getdate())
ORDER BY 1

这给了我以下结果:

age_in_months       number_of_valid_emails
-----------------------------
0                   63
1                   2066
2                   1528
3                   2449
4                   1882
5                   1835
6                   1772
7                   2190
8                   2321
9                   2172
10                  2635
11                  1336
12                  601

我需要得到的是所有这些数字的总和(总计)。我很乐意将它放在底部，甚至只是返回一个数字。

所以像这样:

age_in_months       number_of_valid_emails
-----------------------------
0                   63
1                   2066
...                 ...
SUM                 2129

如有任何帮助，我们将不胜感激。

---

它到达那里，我的 SQL 查询现在看起来像这样:

SELECT DATEDIFF(m, originalretaildate, getdate()) AS age_in_months,
count(email) AS number_of_valid_emails
FROM #result2
WHERE originalretaildate BETWEEN '20140415' AND GETDATE()
GROUP BY DATEDIFF(m, originalretaildate, getdate()) WITH ROLLUP
ORDER BY 2 ASC

这给了我这个:

0   65
--------
12  598
11  1329
2   1530
6   1777
5   1830
4   1875
1   2049
9   2176
7   2200
8   2319
3   2442
10  2631
NULL    22821

我现在的问题是它在最后一行显示“NULL”。有什么办法可以解决这个问题，让它显示“总计”或“总和”之类的内容吗？

Answer 1

根据我的理解，您想在底部汇总值。您可以使用汇总来获取底部的值总和。

SELECT DATEDIFF(m, originalretaildate, getdate()) AS age_in_months,
count(email) AS number_of_valid_emails
FROM #result2
WHERE originalretaildate BETWEEN '20140415' AND GETDATE()
GROUP BY DATEDIFF(m, originalretaildate, getdate()) with rollup
ORDER BY 1

它将产生如下输出。在底部所有值的总和

age_in_months       number_of_valid_emails
-----------------------------
0                   63
1                   2066
2                   1528
3                   2449
4                   1882
5                   1835
6                   1772
7                   2190
8                   2321
9                   2172
10                  2635
11                  1336
12                  601

---

null                22850

编辑 1

根据您的编辑，您可以像这样使用COALESCE

SELECT COALESCE(DATEDIFF(m, originalretaildate, getdate()),'Total') AS age_in_months,
count(email) AS number_of_valid_emails
FROM #result2
WHERE originalretaildate BETWEEN '20140415' AND GETDATE()
GROUP BY DATEDIFF(m, originalretaildate, getdate()) with rollup
ORDER BY 1

编辑 2

使用 cast 到你的月数，然后使用 coalesce() 函数，这样它就能以正确的方式转换它

 cast(DATEDIFF(m, originalretaildate, getdate()) as nvarchar(10))

整个查询将是这样的

SELECT COALESCE(cast(DATEDIFF(m, originalretaildate, getdate()) as nvarchar(10)),'Total') AS age_in_months,
count(email) AS number_of_valid_emails
FROM #result2
WHERE originalretaildate BETWEEN '20140415' AND GETDATE()
GROUP BY DATEDIFF(m, originalretaildate, getdate()) with rollup
ORDER BY 1