asked on

Get count of records by month using SQL

I have a date range of records. (1/1/2010 through 12/31/2010)

Coding the following will give me a total record count:

=Count([MyDateField])

How could I modify it to give me a count by month? I need to know the number of records per month (ie 1/1/2010 through 1/31/2010, 2/1/2010 through 2/28/2010, etc etc)

anillucky31

select Month(MyDateField), COUNT(1) as monthcount from yourtable
group by Month(MyDateField)

ASKER CERTIFIED SOLUTION

anillucky31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ephraim Wangoya

try

SELECT DATENAME(MM, mydatefield), DATEPART(YY, mydatefield), COUNT(*)
from CABINET
group by DATENAME(MM, mydatefield), DATEPART(YY, mydatefield)

Open in new window

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

or use round or trunc

select months_between(to_date('12/31/2010','mm/dd/yyyy'), to_date('1/1/2010','mm/dd/yyyy')) from dual
11.9677419354839
select round(months_between(to_date('12/31/2010','mm/dd/yyyy'), to_date('1/1/2010','mm/dd/yyyy'))) from dual
12
select trunc(months_between(to_date('12/31/2010','mm/dd/yyyy'), to_date('1/1/2010','mm/dd/yyyy'))) from dual
11

HainKurt

you want grouping I guess

select count(1), to_char(start_date,'yyyymm')
from myTable
group by to_char(start_date,'yyyymm')
order by 2

12 200604
25 200605
38 200607
...

HainKurt

and a where condition of course

select count(1), to_char(start_date,'yyyy/mm')
from myTable
where start_date between to_date('1/1/2010','mm/dd/yyyy') and to_date('12/31/2010','mm/dd/yyyy')
group by to_char(start_date,'yyyy/mm')
order by 2

SOLUTION

Cboudroz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial