T-SQL- include sum of count(*) in single query

Question

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

This produces:

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

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.

Answer 1

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