count records per month each year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

count records per month each year

Author

Topic

iscode
Starting Member

4 Posts

Posted - 2004-05-20 : 05:37:53

I am trying to count records per month using asp and mdb accesss

looking like this:

2004 May (3)
2004 April (7)
2004 March (4)
2004 February (8)
2004 Januar (15)
2003 December (12)
2003 November (6)

I have this so far, just counting Year(Now) but need it for all years

strSQL="SELECT DISTINCTROW Data.Month, Count(*) AS iCount FROM [Data] WHERE [Year]=Year(Now()) GROUP BY Data.Month"

Do Until (rst.EOF or rst.BOF)

sYear=year(Now())
sMonth=cstr(rst("Month"))
sCount=cstr(rst("iCount"))
Response.Write sYear & " " & monthname(cint(sMonth)) & " (" & sCount & ")"
rst.movenext
loop

How do I manage this for each year too?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-20 : 05:50:37

[code]
select
month(Data.Month),
year(Data.Month),
count(*)
from [Data]
group by
month(Data.Month),
year(Data.Month)
[/code]

Assuming Month is actually a date field...

EDIT:Just realised this is an access question, the above is SQL...

iscode
Starting Member

4 Posts

Posted - 2004-05-20 : 06:09:39

How do you resolve the loop and etc.

Do Until (rst.EOF or rst.BOF)

sYear=? <----?
sMonth=? <---?
sCount=cstr(rst("iCount"))
Response.Write sYear & " " & monthname(cint(sMonth)) & " (" & sCount & ")"
rst.movenext
loop

iscode
Starting Member

4 Posts

Posted - 2004-05-20 : 06:26:13

sorry I forgot to mention:

Year is number field of year (like 2003)
Month is number field of month (like 5)
Day is number field of day (like 12)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-20 : 07:00:49

[code]
select
Data.Month, Data.Year, count(*)
from [Data]
group by
Data.Month, Data.Month
[/code]

That will get the values from the table...

sYear=rst("Year")
sMonth=rst("Month")

I am still assuming you keep year in the table...

iscode
Starting Member

4 Posts

Posted - 2004-05-20 : 07:28:08

thank you RickD
I just solved this few min ago finaly :)

I did: strSQL="SELECT Data.Month,Data.Year, Count(*) AS iCount FROM [Data] GROUP BY Data.Month,Data.Year Order by Data.Year Desc, Data.Month"

hope this helps someone else

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-20 : 08:29:05

Which is the same as my query...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources