Rank group by, Sql Server

Rank group by

CREATE TABLE ForgeRock
    (
     [Tnr] integer, 
     [Id] integer,
     [Dt] date,
     [val] int
    )
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (1, 1,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (2, 2,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (3, 3,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (4, 1,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (5, 2,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (6, 3,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (7, 1,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (8, 2,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (9, 3,'2018-05-01', 100)
;

INSERT INTO ForgeRock
    ([Tnr], [Id], [Dt], [val])
VALUES
    (10, 1,'2018-05-01', 100)
;

WITH T AS (
    SELECT RANK() OVER (ORDER BY Tnr) Rank, Tnr ,id,Dt, val AS val
    FROM ForgeRock
    )
    SELECT (Rank-1) / 3 GroupID, MAX(id),Dt, SUM(val) AS val ,count(Tnr) as Record
    FROM T
    GROUP BY  ((Rank-1) / 3),Dt
    order By  GroupID


run \| edit \| history \| help	0