but I'm sure that wont exactly what OP is looking for. As I understand attempt is to get SUM of Units for distinct comvbination of IDs. But taking just distinct over sum will cause it to consider unit value alone.see difference below. declare @test table(ID int, CB int, Date_CB datetime, CN decimal(8,2), Date_CN datetime, Units decimal(8,2))insert @testselect 1, 25, '2012-12-12', 23.8, '2012-12-12', 100.65 union allselect 1, 25, '2012-12-27', 23.8, '2012-12-27', 100.65 union allselect 2, 50, '2012-12-30', 46.7, '2012-12-30', 250.78 union allselect 3, 55, '2012-02-17', 42.3, '2012-02-17', 250.78 select SUM(DISTINCT Units) AS DistSumOfUnits,SUM(CASE WHEN Seq=1 THEN Units ELSE 0 END) AS SumUnitsForDistIDfrom (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq FROM @test)toutput----------------------------------DistSumOfUnits SumUnitsForDistID----------------------------------351.43 602.21
I think OP wants latter which only will make sense------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/