MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


CREATE TABLE `deine_tabelle` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `datum` DATETIME NOT NULL,
    `Nr1` INT NOT NULL,
    `Nr2` INT NOT NULL,
    `Nr3` INT NOT NULL,
    `Nr4` INT NOT NULL,
    `Nr5` INT NOT NULL
);

INSERT INTO `deine_tabelle` (`datum`, `Nr1`, `Nr2`, `Nr3`, `Nr4`, `Nr5`)
VALUES
    ('2024-06-25 00:00:00', 158181, 1100, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1110, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1120, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1130, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1140, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1150, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1150, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1160, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1170, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1180, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1190, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1200, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1200, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1200, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1210, 6930, 48276575, 8),
    ('2024-06-25 00:00:00', 158181, 1210, 6930, 48276575, 8);

Records: 16  Duplicates: 0  Warnings: 0




Query was empty


WITH t AS (
SELECT Nr1,Nr2,Nr3,Nr4,Nr5,
ROW_NUMBER() OVER (PARTITION BY Nr1,Nr4,Nr5,Nr2 ORDER BY Nr2) AS zeile
FROM deine_tabelle
)
SELECT COUNT(*),SUM(Nr3),GROUP_CONCAT(Nr2 ORDER BY Nr2 ASC)
FROM t
GROUP BY Nr1,Nr4,Nr5,zeile


COUNT(*)
SUM(Nr3)
GROUP_CONCAT(Nr2 ORDER BY Nr2 ASC)


12
83160
1100,1110,1120,1130,1140,1150,1160,1170,1180,1190,1200,1210

3
20790
1150,1200,1210

1
6930
1200


SELECT SUM(1), GROUP_CONCAT(Nr2 ORDER BY Nr2 ASC) AS cc,max(Nr3),max(Nr4) FROM (
SELECT 
    *
  , (IF(@last_nr2 = Nr2, @grp_nr:=@grp_nr+1, @grp_nr)) AS grp_nr
  , (@last_nr2 := Nr2) AS last_nr2
FROM deine_tabelle
CROSS JOIN ( SELECT @grp_nr:=1 , @last_nr2:='' ) AS Init
) as x
GROUP BY Nr1, grp_nr, Nr3, Nr4, Nr5 ;





SUM(1)
cc
max(Nr3)
max(Nr4)


6
1100,1110,1120,1130,1140,1150
6930
48276575

6
1150,1160,1170,1180,1190,1200
6930
48276575

1
1200
6930
48276575

2
1200,1210
6930
48276575

1
1210
6930
48276575

COUNT(*)	SUM(Nr3)	GROUP_CONCAT(Nr2 ORDER BY Nr2 ASC)
12	83160	1100,1110,1120,1130,1140,1150,1160,1170,1180,1190,1200,1210
3	20790	1150,1200,1210
1	6930	1200

SUM(1)	cc	max(Nr3)	max(Nr4)
6	1100,1110,1120,1130,1140,1150	6930	48276575
6	1150,1160,1170,1180,1190,1200	6930	48276575
1	1200	6930	48276575
2	1200,1210	6930	48276575
1	1210	6930	48276575