Some MSSQL query wizardry

I have a table that has data with entry dates back through the years.  Is there a way to sort data into year/month order?  So if I am counting the number of transactions per day I would get data returned like this?  

Month  Day  Count

1           1       56    
1           2       60
1           3       72

I don't have a sample table to work off of, I have just been wondering if there was an efficiant way to accomplish this.

Thanks!


Fox
LVL 9
the_b1ackfoxCIOAsked:
Who is Participating?
 
Pratima PharandeCommented:
Consider table structure like this

Table one
dt             - datetime
id            

then in this case if you need to count the how many id's per year month day
try this query

select year(dt) , month(dt) , day(dt) , count(id) from Tablename
group by year(dt) , month(dt) , day(dt)
order by year(dt) , month(dt) , day(dt)
0
 
the_b1ackfoxCIOAuthor Commented:
I tested this solution last night (I found a table to play with).  Not only did it work great, it pointed out why my previous attemps to do this had failed.  I feel like I just learned a truly worthwhile lesson.  Thank you Pratima!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.