Query with counts per group by date

Greetings.

I have a table:

ID
Field1
Timestamp (which is a populated with getdate())

I want to do a query that will respond with
date   count
10/1   4
10/2   4
10/3   52
etc.

Thanks,
John
rugby148Asked:
Who is Participating?
 
SwindleConnect With a Mentor Commented:
SELECT CONVERT(varchar, Timestamp, 101) AS [Date], COUNT(*) AS Qty
FROM YourTable
GROUP BY CONVERT(varchar, Timestamp, 101)
0
 
Patrick MatthewsConnect With a Mentor Commented:
SELECT CONVERT(datetime, CONVERT(varchar, Timestamp, 101)) AS [Date], COUNT(*) AS Qty
FROM YourTable
0
 
Patrick MatthewsCommented:
rugby148,

The only difference in the two approaches above is that mine returns a datetime for the Date
(although the time portion will always be midnight) while Swindle's returns a varchar.
To-MAY-to, to-MAH-to, really :)

Regards,

Patrick
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SwindleCommented:
And yours won't work because you left out the GROUP BY clause.    It won't parse.
0
 
Patrick MatthewsCommented:
Swindle said:
>>And yours won't work because you left out the GROUP BY clause.    It won't parse.

Touche :)
0
 
rugby148Author Commented:
why the 101?
0
 
Patrick MatthewsCommented:
rugby148,

It's a code that specifies a particular format for the results of the CONVERT expression.  101
specifies mm/dd/yyyy; any of the formats that leave off the time portion would have sufficed
for my purpose.

Regards,

Patrick
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.