• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

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
0
rugby148
Asked:
rugby148
  • 4
  • 2
2 Solutions
 
Patrick MatthewsCommented:
SELECT CONVERT(datetime, CONVERT(varchar, Timestamp, 101)) AS [Date], COUNT(*) AS Qty
FROM YourTable
0
 
SwindleCommented:
SELECT CONVERT(varchar, Timestamp, 101) AS [Date], COUNT(*) AS Qty
FROM YourTable
GROUP BY CONVERT(varchar, Timestamp, 101)
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now