SQL Server multiple partitions by month, day and hour

Question

In SQL Server I have a table like below:

processName   initDateTime           
processA      2020-06-15 13:31:15.330
processB      2020-06-20 10:00:30.000
processA      2020-06-20 13:31:15.330
...
and so on

I need to group by processName and for each processName I need to get the number of records by month (#byMonth), day (#byDay) and hour (#byHour).

What is the best way to do it? Something as below? What would be the SQL query?

Possible results:

processName Month    Day  Hour  #byMonth #byDay #byHour #total(by process)
processA    January  15   17    4         3     2       7
processA    January  15   20    4         3     1       7
processA    January  20   05    4         2     3       7
processA    January  20   13    4         2     1       7
processA    March    04   05    3         2     3       7
processA    March    04   17    3         2     2       7
processA    March    15   05    3         3     3       7

...and so on for the rest of processes name

Answer 1

I think that you want aggregation and window functions:

select 
    processName,
    month(initDateTime),
    day(initDateTime),
    datepart(hour, initDateTime),
    sum(count(*)) over(partition by processName, year(initDateTime), month(initDateTime)) byMonth,
    sum(count(*)) over(partition by processName, year(initDateTime), month(initDateTime), day(initDateTime)) byDay,
    count(*) byHour
from mytable
group by 
    processName,
    year(initDateTime),
    month(initDateTime),
    day(initDateTime),
    datepart(hour, initDateTime)