问题:

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.

回答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

回答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

回答3:

Create a sub query and include the results in your 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