declare @drug table 
(
    drug_id varchar(3),
    batch_code varchar(4),
    invoice_number varchar(4),
    number int,
    create_date datetime
)
insert into @drug
select '001','p001','f123',10,null union all
select '001','p001','f123',20,null union all
select '001','p001','f123',30,null union all
select '001','p001','f123',40,null union all
select '001','p002','f123',10,null

select 
    drug_id,batch_code,invoice_number,number=sum(number) 
from @drug 
group by drug_id,batch_code,invoice_number
/*
drug_id batch_code invoice_number number
------- ---------- -------------- -----------
001     p001       f123           100
001     p002       f123           10
*/

------解决方案--------------------
select drug_id ,batch_code,invoice_number, sum(number) from drug group by drug_id ,batch_code,invoice_number

------解决方案--------------------
create table tbl  
(
   drug_id varchar(3),
   batch_code varchar(4),
   invoice_number varchar(4),
   number int,
   create_date datetime
)
insert tbl  
select '001','p001','f123',10,null union all
select '001','p001','f123',20,null union all
select '001','p001','f123',30,null union all
select '001','p001','f123',40,null union all
select '001','p002','f123',10,null

select  
   drug_id,batch_code,invoice_number,number=sum(number)  
from tbl  
group by drug_id,batch_code,invoice_number
/*
结果表：
drug_id batch_code invoice_number number
001     p001       f123           100
001     p002       f123           10
*/


------解决方案--------------------
select drug_id ,batch_code,invoice_number, sum(number) number
from drug
group by drug_id ,batch_code,invoice_number

------解决方案--------------------
SQL code
create table drug
(drug_id varchar(10),
 batch_code varchar(10),
 invoice_number varchar(10),
 number int,
 create_date datetime)
go
insert into drug
select '001','p001','f123',10,null union all
select '001','p001','f123',20,null union all
select '001','p001','f123',30,null union all
select '001','p001','f123',40,null union all
select '001','p002','f123',10,null
go
select drug_id,batch_code,invoice_number,number=SUM(number) from drug group by drug_id,batch_code,invoice_number
go
drop table drug