I have the same problem with this i want to get those duplicate values and i successfully done it using this code

SELECT projNum, ProjCode, LaborCode, Code, Description, Price, QtyMaterials, AIPrice
FROM Cost_MasterMaterials
WHERE Code IN (
    SELECT Code
    FROM Cost_MasterMaterials
    GROUP BY Code
    HAVING COUNT(*) > 1
)

but my problem is after i get the details of the duplicate values and all its field i want to sum the QTYMaterials then retain only one item of every duplicate values.

Sample Table

id | projnum | projcode | code | description | Price | QtyMaterials | AIPrice, 
1      23        Null     1207    Dog           Null       1          Null
2      23        Null     1207    Dog           Null       2          Null
3       2        Null     1321    Pig           Null       5          Null
4      12        Null     1621    Cat           Null       6           Null
5       2        Null     1321    Pig           Null       1          Null

Result must be like this

id | projnum | projcode | code | description | Price | QtyMaterials | AIPrice, 
1      23        Null     1207    Dog           Null       3          Null
3       2        Null     1321    Pig           Null       6          Null
4      12        Null     1621    Cat           Null       6          Null

I Hope someone can help me of this.

CodePudding user response：

You can use SUM with GROUP BY

Try this

SELECT 
    projNum, ProjCode, LaborCode, Code, Description, 
    Price, SUM(QtyMaterials) QtyMaterials, AIPrice
FROM Cost_MasterMaterials
GROUP BY projNum, ProjCode, LaborCode, Code, Description, Price, AIPrice

CodePudding user response：

What you are trying to do is basically you need aggregated value of the Materials or any aggregation field of you choice.

In your case you want to see what are the duplicate records by at code level basically. So you can do this by looking at your data, one is include all the fields in group by clause - given that all the other values associated are same, you end up having only 1 record ( which will also delete the duplicate at your level) or you can do the MIN/MAX operation on the string fields as well to uniquely get the records at the CODE level (Provided you have only 1-1 mapping of Code to rest of the attributes).

1. Option 1 is already highlighted by Nayanish Damania. Use the script
2. Option 2

SELECT MIN(ID) as ID, MIN(PROJNUM) as PROJNUM, ProjCode ,MIN(description) as description , mIN(Price) as PRICE , MIN(Price) as PRICE, SUM(QtyMaterials) as QtyMaterials FROM Cost_MasterMaterials GROUP BY ProjCode ORDER BY MIN(ID) ASC;