求一查询语句 感谢

[oqqken12345]

表device
device_code,device_name,device_type,type_name
FH-0001, 磨床, M01, 机加工类
FH-0002, 车床, M01, 机加工类
FH-0003 钻床, M02, 机加工类

求根据device_type相同的求和统计显示为：
device_code,device_name,device_type,type_name， count
FH-0001, 磨床, M01, 机加工类, 2
FH-0002, 车床, M01, 机加工类， 2
FH-0003 钻床, M02, 机加工类， 1

我知道用group by,我想保留每行的device_code。谢谢

[liu志坚]

select device_code, device_name, device_type, type_name, (select count(*) from device b where a.device_type = b.device_type) ct from device a;

[花开了叫我]

count(1) over (partiiton by device_type)