-1

Here is my data table:

userid  timestamp
------------------------
user1   2017-08-15 17:00
user1   2017-08-15 17:00
user1   2017-08-15 17:00
user2   2017-08-15 17:00
user2   2017-08-15 17:00
user3   2017-08-15 17:00
user1   2017-08-15 18:00
user1   2017-08-15 18:00
user2   2017-08-15 18:00
user2   2017-08-15 18:00
user2   2017-08-15 18:00
user3   2017-08-15 18:00

I want the result to look like this:

userid    countoftimestamp
-----------------------------
user1     3_2017-08-15 17:00
user2     2_2017-08-15 17:00
user3     1_2017-08-15 17:00
user1     2_2017-08-15 18:00

and so on.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Syed Umair Rizvi
  • 5
  • 7
  • 5
    "I need a query" is not a question. Have you considered hiring a programmer? – melpomene Aug 15 '17 at 15:26
  • 1
    This is not a spoonfeeding site. Please show what you attempted and what happened with that attempt. – Dan Bracuk Aug 15 '17 at 15:26
  • Don't `concat` the count onto the time-stamp... keep it in a separate column so you don't have to parse it later to use it... and so you don't change the datatype of the time-stamp. With that said, both solutions below answer the question, and this is a duplicate of 100 other questions. – S3S Aug 15 '17 at 15:46
  • Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) – S3S Aug 15 '17 at 15:47
  • Wrong link for the duplicate, though it is as well.[Here is a better duplicate post](https://stackoverflow.com/questions/887822/sql-server-server-query-count-distinct-datetime-field) – S3S Aug 15 '17 at 15:48
  • @scsimon While I agree with you 100%, I've had to feed downstream applications with some ridiculous requirements and formats. – John Cappelletti Aug 15 '17 at 15:52
  • True @JohnCappelletti and hence my +1 on your post, but i'd bet a bounty that isn't the case here :D – S3S Aug 15 '17 at 15:53
  • @scsimon lol.. I'm sure you are correct – John Cappelletti Aug 15 '17 at 15:54

2 Answers2

2
SELECT userid, timestamp, count(*)
FROM datatabable
GROUP BY  userid, timestamp
Andrew Richards
  • 59
  • 2
1

You may notice that I used varchar(16) to truncate your timestamp down to minutes (excluding seconds and milliseconds)

Example

Select userid
      ,countoftimestamp = cast(sum(1) as varchar(25))+'_'+convert(varchar(16),timestamp,20)
 From  YourTable
 Group By userid,convert(varchar(16),timestamp,20)

Returns

enter image description here

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66