Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
BRYAN
sql test
Students
TSQL - Globally unique string generator
aa
SQL Stringdata
Using Variables
2 лаба
mywall
Display Multiple Rows as a single Line