MySQL 8.0

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

create table test(
  doctor_id int,
  line_product_id int,
  line_product_name varchar(52)
)

insert into test values (1, 7, 'seven'),
(1, 6, 'six'),
  (1, 6, 'six'),
  (1, 7, 'seven'),
  (1, 7, 'seven'),
  (1, 7, 'seven'),
  (1, 6, 'six');;

Records: 7  Duplicates: 0  Warnings: 0


select * from test;


doctor_id
line_product_id
line_product_name


1
7
seven

1
6
six

1
6
six

1
7
seven

1
7
seven

1
7
seven

1
6
six


select 
  doctor_id, 
  line_product_id, 
  line_product_name,
  count(line_product_id) as qty
from test
group by doctor_id, line_product_id, line_product_name
order by doctor_id asc;



doctor_id
line_product_id
line_product_name
qty


1
6
six
3

1
7
seven
4