By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sua_tabela(id int, codnome int, nome varchar(10), disc varchar(10), nota varchar(4), qtd int);
insert into sua_tabela(id, CodNome, Nome, Disc, Nota) values
(1, 123, 'A', 'MATE', 6),
(2, 123, 'A', 'MATE', 7),
(3, 123, 'A', 'PORT', 8),
(4, 123, 'A', 'PORT', 9),
(5, 345, 'M', 'MATE', 9),
(6, 345, 'M', 'MATE', 8),
(7, 345, 'M', 'MATE', 7),
(8, 345, 'M', 'PORT', 7),
(9, 345, 'M', 'CIEN', 7)
SELECT t.id, t.CodNome, t.Nome, t.Disc, t.Nota, s.qtd
FROM sua_tabela t
JOIN (
SELECT CodNome, Nome, Disc, COUNT(*) AS qtd
FROM sua_tabela
GROUP BY CodNome, Nome, Disc
) s
ON t.CodNome = s.CodNome AND t.Nome = s.Nome AND t.Disc = s.Disc
ORDER BY t.id;
| id | CodNome | Nome | Disc | Nota | qtd |
|---|---|---|---|---|---|
| 1 | 123 | A | MATE | 6 | 2 |
| 2 | 123 | A | MATE | 7 | 2 |
| 3 | 123 | A | PORT | 8 | 2 |
| 4 | 123 | A | PORT | 9 | 2 |
| 5 | 345 | M | MATE | 9 | 3 |
| 6 | 345 | M | MATE | 8 | 3 |
| 7 | 345 | M | MATE | 7 | 3 |
| 8 | 345 | M | PORT | 7 | 1 |
| 9 | 345 | M | CIEN | 7 | 1 |