CODEWITHSUNDEEP
sqlsql-serverdatetime
firestruq
firestruq

Reputation: 739

Select count for each specific date

I have the following need: I need to count the number of times each id activated from all dates.

Let's say the table looks like this:

tbl_activates
PersonId int,
ActivatedDate datetime

The result set should look something like this:

counted_activation | ActivatedDate
5             | 2009-04-30
7             | 2009-04-29
5             | 2009-04-28
7             | 2009-04-27

... and so on

Anyone know how to do this the best possible way? The date comes in the following format '2011-09-06 15:47:52.110', I need to relate only to the date without the time. (summary for each date)

Upvotes: 0

Views: 1079

Answers (3)

ITSGuru
ITSGuru

Reputation: 194

Use 'GROUP BY' and 'COUNT'. Use CONVERT method to convert datetime to Date only

  SELECT CONVERT(DATE,activatedate), COUNT(userId)
  FROM [table]
  GROUP BY  CONVERT(DATE,InvoiceDate)

Upvotes: 0

Guru0008
Guru0008

Reputation: 54

You can use to_char function to remove the time from date

    select count(*) counted_activation, 
to_char(activatedDate,"yyyy-mm-dd") ActDate
    from table1
    group by to_char(activatedDate,"yyyy-mm-dd");

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

you can use count(distinct .. )

and if the ActivatedDate is datetime you can get the date part 

  select  Cast(ActivatedDate AS date), count(distinct id)
  from my_table 
  group by  ast(ActivatedDate AS date)

Upvotes: 1

Related Questions