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.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
Consider table structure like this

Table one
dt             - datetime

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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.