SQL Server 2019

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

CREATE TABLE [dbo].[Printer](
	[code] [int] NOT NULL,
	[model] [varchar](50) NOT NULL,
	[color] [char](1) NOT NULL,
	[type] [varchar](10) NOT NULL,
	[price] [money] NULL,

) 
INSERT INTO [dbo].[Printer]
           ([code]
           ,[model]
           ,[color]
           ,[type]
           ,[price])
     VALUES
           (1 
           ,'1276' 
           ,'n'
           ,'Laser'
           ,400 )


,(2 
           ,'1433' 
           ,'y'
           ,'Jet'
           ,270 )
,(3 
           ,'1434' 
           ,'y'
           ,'Jet'
           ,290 )
           
           
           ,(4 
           ,'1401' 
           ,'n'
           ,'Matrix'
           ,150 )
           
                ,(5 
           ,'1408' 
           ,'n'
           ,'Matrix'
           ,270 )
           
                ,(6 
           ,'1288' 
           ,'n'
           ,'Laser'
           ,400 )

6 rows affected

select * from printer


code
model
color
type
price


1
1276
n
Laser
400.0000

2
1433
y
Jet
270.0000

3
1434
y
Jet
290.0000

4
1401
n
Matrix
150.0000

5
1408
n
Matrix
270.0000

6
1288
n
Laser
400.0000


SELECT * ,
 dense_rank() over (partition by  [group] order by  TYPE) + dense_rank() over (partition by  [group] order by  TYPE desc) - 1 as distinct_type_cnt,
 avg(price) over(partition by  [group]) avg_price
from (select *, 
sum(case color when 'n' then 1 else 0 end) over (order by code) as [group]

from printer ) AS printer
order by code


code
model
color
type
price
group
distinct_type_cnt
avg_price


1
1276
n
Laser
400.0000
1
2
320.0000

2
1433
y
Jet
270.0000
1
2
320.0000

3
1434
y
Jet
290.0000
1
2
320.0000

4
1401
n
Matrix
150.0000
2
1
150.0000

5
1408
n
Matrix
270.0000
3
1
270.0000

6
1288
n
Laser
400.0000
4
1
400.0000

code	model	color	type	price
1	1276	n	Laser	400.0000
2	1433	y	Jet	270.0000
3	1434	y	Jet	290.0000
4	1401	n	Matrix	150.0000
5	1408	n	Matrix	270.0000
6	1288	n	Laser	400.0000