T-SQL- includes the sum of count (*) in a single query

Question

Using the table I and the date_entered fields and the code, I wrote a query to list the bill 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 gives:

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

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

Answer 1

Add WITH ROLLUP to the query after the GROUP BY , and you will get an extra row with NULL Yr that contains the final amount.

 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 

Answer 2

 ;WITH cte AS (SELECT YEAR(date_entered) AS yr, COUNT(*) AS cnt FROM i WHERE code = '12A' GROUP BY YEAR(date_entered)) SELECT yr, cnt, SUM(cnt) OVER () AS totcnt FROM cte ORDER BY yr DESC 

Answer 3

Create an additional query and include the results in the main query

 select year(date_entered) as Yr, count(*) as Cnt, t.MySum from i INNER JOIN ( SELECT SUM(MyColumn) as MySum FROM i WHERE code='12A' ) t ON t.ID = MyTable.ID where code = '12A' group by year(date_entered) order by Yr desc