SQL Query to remove duplicated data and take single column sum

I have the following table resulted from

SELECT m.MedName as [Medicine],m.MedSellPrice as [RetailPrice],m.MedType as [Type],
       m.SoldQuantity as [Sold],m.Quantity as [Available],b.BillAmount as [Total Bill],b.BillDate
FROM BillMedicine AS bm LEFT JOIN
     Medicine AS m  
     ON bm.MedicineID=m.id LEFT JOIN
     Bill AS b
     ON bm.BilIID = b. ID

but now I want to remove the repeated rows except the Sum of ‘TotalBill’.

Answer

Use GROUP BY:

SELECT
    m.MedName AS [Medicine],
    m.MedSellPrice AS [RetailPrice],
    m.MedType AS [Type],
    m.SoldQuantity AS [Sold],
    m.Quantity AS [Available],
    SUM(b.BillAmount) AS [Total Bill]
FROM BillMedicine AS bm
LEFT JOIN Medicine AS m
    ON bm.MedicineID = m.id
LEFT JOIN Bill AS b
    ON bm.BilIID = b.ID
GROUP BY
    m.MedName,
    m.MedSellPrice,
    m.MedType,
    m.SoldQuantity,
    m.Quantity;

Note that for the billing date, the two “duplicate” records you have highlighted have different dates. It is not clear which date, if any, you want to report here. I have omitted this column.