SELECT T1.BARCODE, T1.U_PRICE, SUM(T1.QTY) AS QTY,
SUM(T1.TOTAL1) AS TOTAL1, T2.Description
FROM TABLE1 T1 INNER JOIN TABLE1 T2
ON T1.BARCODE=T2.BARCODE

WHERE T1.OPERA1="SELL" AND T2.OPERA1="BUY"

GROUP BY T1.BARCODE, T1.U_PRICE, T2.Description

select
   T1.BarCode,
   T1.U_PRICE,
   T2.Description,
   SUM(T1.QTY) AS QTY,
   SUM(T1.TOTAL1) AS TOTAL1
from
   Table1 T1
group by
   T1.BarCode,
   T1.U_PRICE,
   T2.Description
   

-- TABLES
create table dbo.Drugs
(
  drug_id int not null primary key,
  drug_name varchar(255) not null,
  drug_barcode varchar(31) not null,
  inventory_balance int not null check (inventory_balance >= 0)
);

CREATE UNIQUE INDEX index_name
ON dbo.Drugs (drug_barcode);

create table dbo.Drug_Transactions
(
   trans_id int not null identity(1,1) primary key,
   drug_barcode varchar(31) not null references drugs(drug_barcode),
   trans_type varchar(15) not null check(trans_type in ('CUST_SALE','CUST_RETURN','SUPPLIER_BUY','SUPPLIER_RETURN')),
   trans_quantity int not null,
   trans_price decimal(9,2) not null,
   trans_date datetime not null default getdate()
)

-- SAMPLE DATA
insert into drugs values (1,'Aspirin',1111,0);

insert into drug_Transactions values (1111,'SUPPLIER_BUY',10,1.00,getdate());
insert into drug_Transactions values (1111,'SUPPLIER_BUY',15,1.00,getdate());
insert into drug_Transactions values (1111,'CUST_SALE',2,1.00,getdate());
insert into drug_Transactions values (1111,'CUST_SALE',3,1.00,getdate());

-- DOWNSIDE OF THE DESIGN IS THAT YOU HAVE TO TAKE CARE TO KEEP THE INVENTORY_BALANCE UPDATED.
update drugs set inventory_balance = 20 where drug_barcode = 1111;

-- QUERY TO FIND CUSTOMER SALES.
select
	m.drug_name,
	m.drug_barcode,
	t.trans_type,
	sum(t.trans_quantity) as Quantity
from
	drugs m
	inner join drug_Transactions t
		on t.drug_barcode = m.drug_barcode
where
	t.trans_type = 'CUST_SALE'
group by
	m.drug_name,
	m.drug_barcode,
	t.trans_type;