T-SQL Query to Get SUM(COUNT())

Question

Hi,

I am planning to get a report for a table with following table structure:

ID              RequestDate
-----------------------------
1               2010/01/01
2               2010/02/14
3               2010/03/20
4               2010/01/07
5               2009/03/31

I want the results as: I

D_Count    RequestDate               Sum
-----------------------------------------
2               2010/01              2
1               2010/02              3
2               2010/03              5

Pls help.

Answer 1

You just have to group by the year and month date parts of the table to get the count per month and year:

select
   count(*), datePart("yy", requestDate) + "/" + datePart("mm", requestDate)
from table1
group by 
datePart("yy", requestDate), datePart("mm", requestDate)

To get the sum of these you would have to have a temp table and then update that temp table sum column with the running total.

create table #temp ( rowID identity(1,1) int, dateCount int, yearMonth varchar(50), runningTotal int)

insert into #temp ( dateCount, yearMonth, runningTotal ) 
    select
       count(*), datePart("yy", requestDate) + "/" + datePart("mm", requestDate)
    from table1
    group by 
    datePart("yy", requestDate), datePart("mm", requestDate)

update #temp set runningTotal = (select sum(dateCount) from #temp a where a.rowID < #temp.rowID)

select * from #temp 

drop table #temp