T-SQL- 在单个查询中包含计数总和(*) [英] T-SQL- include sum of count(*) in single query

问题描述

使用表 i 以及字段 date_entered 和 code，我编写了一个查询来列出每年的计数，其中 code = '12A'.

Using table i and the fields date_entered and code, I wrote a query to list a count for each year where code = '12A'.

select distinct year(date_entered) as Yr, count(*) as Cnt
from i
where code = '12A'
group by year(date_entered)
order by Yr desc

这会产生:

Yr   |    Cnt
2011   |  780
2010   |  3489
2009   |  3256
...

我想在我的结果集中包含 Cnt 变量的总和.我知道如何使用单独的查询找到总和，但我想计算原始查询中的总和.

I want to include a sum of the Cnt variable in my result set. I know how to find the sum using a separate query, but I would like to calculate the sum in my original query.

推荐答案

在 GROUP BY 子句之后添加 WITH ROLLUP 到查询，你会得到一个额外的行包含您的最终总数的 NULL Yr.

Add WITH ROLLUP to the query after the GROUP BY clause and you'll get an extra row with a NULL Yr that contains your final total.

select year(date_entered) as Yr, count(*) as Cnt
from i
where code = '12A'
group by year(date_entered)
with rollup
order by Yr desc

这篇关于T-SQL- 在单个查询中包含计数总和(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！