CODEWITHSUNDEEP
sqlsql-serverselect
Willy
Willy

Reputation: 10660

SQL Server Grouping rows by datetime and get the total using count

I have a table with some fields and one of them is of type datetime:

Id  When                   typeOfAction 
1   01/02/2020 10:30:02    7
2   01/02/2020 10:30:02    7
3   01/02/2020 10:30:02    7
4   01/02/2020 12:15:00    7
5   01/02/2020 12:15:00    7
6   10/03/2020 17:00:45    7

I want to obtain the number of rows of typeOfAction=7 that there are for a concrete day. The rows that have the same date and time should be counted as 1 row.

For example, If I want to obtain how many rows of typeOfAction=7 there are for 01/02/2020 I am expecting to obtain a count of 2 because the rows with the same date and time 01/02/2020 10:30:02 should be counted as 1 and the same for 01/02/2020 10:30:02.

Upvotes: 1

Views: 29

Answers (2)

casenonsensitive
casenonsensitive

Reputation: 950

You can do that with analytical functions like this:

select t.*, count(*) over (partition by typeOfAction, cast(when as date)) cnt from t

The cast of the datetime will remove the time part in order to find the data on the same day.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312267

You could extract the date and group by it, and then count the distinct datetimes per date:

SELECT   CAST(when AS DATE), COUNT(DISTINCT when)
FROM     mytable
WHERE    typeOfAction = 7
GROUP BY CAST(when AS DATE)

Upvotes: 1

Related Questions