[Resolved] SELECT statement

1

Hi,

I have a table as shown

ID             System Time
34             2009-01-31 14:07:40.263
11             2009-01-31 19:54:38.460
16             2009-02-01 08:58:00.237
10             2009-02-01 15:02:55.757
21             2009-02-01 20:53:33.720
25             2010-10-30 17:16:16.130
109           2010-10-27 17:38:09.380
561           2011-09-31 00:06:22.317

Results expected:

Count                 Month                 Year
2                         01                       2009
3                         02                       2009
2                         10                       2010
1                          09                      2011

Please note, count = SUM of the IDs in that month, Month & Year extracted from System Time. Also, this is just a sample the statement will be executed against millions of records, therefore, expecting count for every calendar month and year.

Thank you in advance

2

Please paste your query so far.

3 
Select SystemTime
, YEAR (SystemTime) AS Year
, MONTH (SystemTime) AS Month
From Db.Test.TblTest

This picks the month and the year. I need the count now...

4

add:

count(id) over(partition by month(systemtime), year(systemtime)) as id_count

5

Thank you - gbritton

Worked perfectly. Much appreciated...

6

np, please mark the thread as solved if you get a chance!