MySQL 8.0

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

CREATE TABLE IF NOT EXISTS `dslam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dslamType_id` int(11) NOT NULL,
  `ip` varchar(30) DEFAULT NULL,
  `sats_id` int(11) NOT NULL,
  `vdsl` varchar(30) DEFAULT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sats_id` (`sats_id`),
  KEY `id` (`id`),
  KEY `dslamType_id` (`dslamType_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO `dslam` (`id`, `dslamType_id`, `ip`, `sats_id`, `vdsl`, `date`) VALUES
(1, 4, '192.168.10.4', 1, '', '2022-03-09 16:53:17'),
(2, 5, '192.168.10.7', 1, '', '2022-03-09 17:35:30'),
(4, 4, '192.168.10.10', 2, '', '2022-03-09 17:35:30'),
(5, 5, '192.172.15.15', 2, '', '2022-03-09 17:35:30'),
(6, 5, '192.172.20.20', 2, '', '2022-03-09 17:35:30'),
(7, 5, '192.168.10.11', 1, '', '2022-03-09 17:35:30'),
(8, 5, '192.168.20.20', 1, '', '2022-03-09 17:35:30');

CREATE TABLE IF NOT EXISTS `dslamtype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `dslamtype` (`id`, `name`) VALUES
(1, '48'),
(2, '64'),
(3, '96'),
(4, '128'),
(5, '288'),
(6, '896');

CREATE TABLE IF NOT EXISTS `sats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `surname` varchar(30) CHARACTER SET utf8mb4 NOT NULL,
  `phone` varchar(30) NOT NULL,
  `login` varchar(30) NOT NULL,
  `dslam_id` int(11) NOT NULL,
  `sats_id` int(11) NOT NULL,
  `port` int(11) NOT NULL,
  `vdsl` varchar(30) DEFAULT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dslam_id` (`dslam_id`),
  KEY `sats_id` (`sats_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

INSERT INTO `sats` (`id`, `name`, `surname`, `phone`, `login`, `dslam_id`, `sats_id`, `port`, `vdsl`, `date`) VALUES
(5, 'dddf', 'sdggg', '(555) 5555', 'rth@dfgsd', 1, 1, 2, '', '2022-03-10 09:59:00'),
(8, 'asdsd', 'asfkk', '(534) 98878', 'drth@FDF', 8, 1, 2, '', '2022-03-10 09:30:56'),
(9, 'sddsg', 'kghl', '(543) 435563', 'dutdty@ýbb', 5, 2, 2, '', '2022-03-10 09:30:56'),
(10, 'qwrr', 'khgm', '(555) 5656', 'rth@dfgsd', 2, 1, 2, '', '2022-03-10 09:48:45'),
(11, 'iujjj', 'mvhh', '(532) 656566', 'pgwwet@ýbb', 5, 2, 2, '', '2022-03-10 10:29:17'),
(12, 'ljhl', 'bbfb', '(555)7-45', 'erwen@ff', 4, 2, 5, '', '2022-03-10 10:34:09'),
(14, 'asaf', 'ascvv', '(555) 867777', 'ddsfsd2SGD', 8, 1, 3, '', '2022-03-10 10:43:11'),
(15, 'yiuj', 'sssdvv', '(555) 545454', 'DSSRR', 2, 1, 3, '', '2022-03-10 10:45:05'),
(16, 'qwf', 'ssdvb', '(555) 545454', 'sdff', 2, 1, 4, '', '2022-03-10 10:45:05'),
(17, 'qwrfg', 'dvvdvs', '(555) 4563', 'zsdd', 2, 1, 5, '', '2022-03-10 10:45:05'),
(18, 'qwrssssssc', 'dsvdb', '(555) 454545', 'sffffe', 0, 1, 23, '', '2022-03-10 10:48:33'),
(19, 'qwf', 'qerbv', '(544) 23443', 'sffff', 1, 1, 23, '', '2022-03-10 10:48:33'),
(20, 'qqqf', 'nnrny', '(544)34643', 'hrthn@ff', 4, 2, 3, '', '2022-03-10 11:16:43'),
(21, 'wfgg', 'fsbsb', '(544) 4545556', 'serhg@ff', 4, 2, 7, '', '2022-03-10 11:17:51');

CREATE TABLE IF NOT EXISTS `sum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

INSERT INTO `sum` (`id`, `name`) VALUES
(1, 'SATS 6/000'),
(2, 'SATS 7/000'),
(23, 'SATS 4/0555'),
(24, 'SATS 7/45455');

-- просмотр отдельных таблиц
SELECT * FROM dslam;
SELECT * FROM dslamtype;
SELECT * FROM sats;
SELECT * FROM `sum`;

Records: 7  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0



Records: 14  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0




id
dslamType_id
ip
sats_id
vdsl
date


1
4
192.168.10.4
1

2022-03-09 16:53:17

2
5
192.168.10.7
1

2022-03-09 17:35:30

4
4
192.168.10.10
2

2022-03-09 17:35:30

5
5
192.172.15.15
2

2022-03-09 17:35:30

6
5
192.172.20.20
2

2022-03-09 17:35:30

7
5
192.168.10.11
1

2022-03-09 17:35:30

8
5
192.168.20.20
1

2022-03-09 17:35:30




id
name


1
48

2
64

3
96

4
128

5
288

6
896




id
name
surname
phone
login
dslam_id
sats_id
port
vdsl
date


5
dddf
sdggg
(555) 5555
rth@dfgsd
1
1
2

2022-03-10 09:59:00

8
asdsd
asfkk
(534) 98878
drth@FDF
8
1
2

2022-03-10 09:30:56

9
sddsg
kghl
(543) 435563
dutdty@ýbb
5
2
2

2022-03-10 09:30:56

10
qwrr
khgm
(555) 5656
rth@dfgsd
2
1
2

2022-03-10 09:48:45

11
iujjj
mvhh
(532) 656566
pgwwet@ýbb
5
2
2

2022-03-10 10:29:17

12
ljhl
bbfb
(555)7-45
erwen@ff
4
2
5

2022-03-10 10:34:09

14
asaf
ascvv
(555) 867777
ddsfsd2SGD
8
1
3

2022-03-10 10:43:11

15
yiuj
sssdvv
(555) 545454
DSSRR
2
1
3

2022-03-10 10:45:05

16
qwf
ssdvb
(555) 545454
sdff
2
1
4

2022-03-10 10:45:05

17
qwrfg
dvvdvs
(555) 4563
zsdd
2
1
5

2022-03-10 10:45:05

18
qwrssssssc
dsvdb
(555) 454545
sffffe
0
1
23

2022-03-10 10:48:33

19
qwf
qerbv
(544) 23443
sffff
1
1
23

2022-03-10 10:48:33

20
qqqf
nnrny
(544)34643
hrthn@ff
4
2
3

2022-03-10 11:16:43

21
wfgg
fsbsb
(544) 4545556
serhg@ff
4
2
7

2022-03-10 11:17:51




id
name


1
SATS 6/000

2
SATS 7/000

23
SATS 4/0555

24
SATS 7/45455


-- просмотр объединённого набора
SELECT *
FROM sats
JOIN dslam ON dslam.id = sats.dslam_id
JOIN dslamtype ON dslam.dslamType_id = dslamtype.id
JOIN `sum` ON sats.sats_id = `sum`.id


id
name
surname
phone
login
dslam_id
sats_id
port
vdsl
date
id
dslamType_id
ip
sats_id
vdsl
date
id
name
id
name


5
dddf
sdggg
(555) 5555
rth@dfgsd
1
1
2

2022-03-10 09:59:00
1
4
192.168.10.4
1

2022-03-09 16:53:17
4
128
1
SATS 6/000

8
asdsd
asfkk
(534) 98878
drth@FDF
8
1
2

2022-03-10 09:30:56
8
5
192.168.20.20
1

2022-03-09 17:35:30
5
288
1
SATS 6/000

10
qwrr
khgm
(555) 5656
rth@dfgsd
2
1
2

2022-03-10 09:48:45
2
5
192.168.10.7
1

2022-03-09 17:35:30
5
288
1
SATS 6/000

14
asaf
ascvv
(555) 867777
ddsfsd2SGD
8
1
3

2022-03-10 10:43:11
8
5
192.168.20.20
1

2022-03-09 17:35:30
5
288
1
SATS 6/000

15
yiuj
sssdvv
(555) 545454
DSSRR
2
1
3

2022-03-10 10:45:05
2
5
192.168.10.7
1

2022-03-09 17:35:30
5
288
1
SATS 6/000

16
qwf
ssdvb
(555) 545454
sdff
2
1
4

2022-03-10 10:45:05
2
5
192.168.10.7
1

2022-03-09 17:35:30
5
288
1
SATS 6/000

17
qwrfg
dvvdvs
(555) 4563
zsdd
2
1
5

2022-03-10 10:45:05
2
5
192.168.10.7
1

2022-03-09 17:35:30
5
288
1
SATS 6/000

19
qwf
qerbv
(544) 23443
sffff
1
1
23

2022-03-10 10:48:33
1
4
192.168.10.4
1

2022-03-09 16:53:17
4
128
1
SATS 6/000

9
sddsg
kghl
(543) 435563
dutdty@ýbb
5
2
2

2022-03-10 09:30:56
5
5
192.172.15.15
2

2022-03-09 17:35:30
5
288
2
SATS 7/000

11
iujjj
mvhh
(532) 656566
pgwwet@ýbb
5
2
2

2022-03-10 10:29:17
5
5
192.172.15.15
2

2022-03-09 17:35:30
5
288
2
SATS 7/000

12
ljhl
bbfb
(555)7-45
erwen@ff
4
2
5

2022-03-10 10:34:09
4
4
192.168.10.10
2

2022-03-09 17:35:30
4
128
2
SATS 7/000

20
qqqf
nnrny
(544)34643
hrthn@ff
4
2
3

2022-03-10 11:16:43
4
4
192.168.10.10
2

2022-03-09 17:35:30
4
128
2
SATS 7/000

21
wfgg
fsbsb
(544) 4545556
serhg@ff
4
2
7

2022-03-10 11:17:51
4
4
192.168.10.10
2

2022-03-09 17:35:30
4
128
2
SATS 7/000


-- подсчёт портов 
SELECT dslam.sats_id, SUM(dslamtype.name) `сумма`
FROM dslam
JOIN dslamtype ON dslam.dslamType_id = dslamtype.id
GROUP BY dslam.sats_id


sats_id
сумма


1
992

2
704


-- подсчёт пользователей
SELECT `sum`.name AS `категория`,
       COUNT(sats.id) AS `пользователи`
FROM sats
JOIN `sum` ON sats.sats_id = `sum`.id
GROUP BY `sum`.name


категория
пользователи


SATS 6/000
9

SATS 7/000
5


-- объединение
SELECT `sum`.name AS `категория`,
       COUNT(sats.id) AS `пользователи`,
       sums.`сумма`
FROM sats
JOIN `sum` ON sats.sats_id = `sum`.id
JOIN ( SELECT dslam.sats_id, SUM(dslamtype.name) `сумма`
       FROM dslam
       JOIN dslamtype ON dslam.dslamType_id = dslamtype.id
       GROUP BY dslam.sats_id ) sums ON sums.sats_id = sats.sats_id
GROUP BY `sum`.name, sums.`сумма`


категория
пользователи
сумма


SATS 6/000
9
992

SATS 7/000
5
704

id	dslamType_id	ip	sats_id	date
1	4	192.168.10.4	1	2022-03-09 16:53:17
2	5	192.168.10.7	1	2022-03-09 17:35:30
4	4	192.168.10.10	2	2022-03-09 17:35:30
5	5	192.172.15.15	2	2022-03-09 17:35:30
6	5	192.172.20.20	2	2022-03-09 17:35:30
7	5	192.168.10.11	1	2022-03-09 17:35:30
8	5	192.168.20.20	1	2022-03-09 17:35:30

id	name	surname	phone	login	dslam_id	sats_id	port	date
5	dddf	sdggg	(555) 5555	rth@dfgsd	1	1	2	2022-03-10 09:59:00
8	asdsd	asfkk	(534) 98878	drth@FDF	8	1	2	2022-03-10 09:30:56
9	sddsg	kghl	(543) 435563	dutdty@ýbb	5	2	2	2022-03-10 09:30:56
10	qwrr	khgm	(555) 5656	rth@dfgsd	2	1	2	2022-03-10 09:48:45
11	iujjj	mvhh	(532) 656566	pgwwet@ýbb	5	2	2	2022-03-10 10:29:17
12	ljhl	bbfb	(555)7-45	erwen@ff	4	2	5	2022-03-10 10:34:09
14	asaf	ascvv	(555) 867777	ddsfsd2SGD	8	1	3	2022-03-10 10:43:11
15	yiuj	sssdvv	(555) 545454	DSSRR	2	1	3	2022-03-10 10:45:05
16	qwf	ssdvb	(555) 545454	sdff	2	1	4	2022-03-10 10:45:05
17	qwrfg	dvvdvs	(555) 4563	zsdd	2	1	5	2022-03-10 10:45:05
18	qwrssssssc	dsvdb	(555) 454545	sffffe	0	1	23	2022-03-10 10:48:33
19	qwf	qerbv	(544) 23443	sffff	1	1	23	2022-03-10 10:48:33
20	qqqf	nnrny	(544)34643	hrthn@ff	4	2	3	2022-03-10 11:16:43
21	wfgg	fsbsb	(544) 4545556	serhg@ff	4	2	7	2022-03-10 11:17:51