SQL - 如何SUM 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

使用投射到您的monthnumber然后用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