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 @test
select 1, 25, '2012-12-12', 23.8, '2012-12-12', 100.65 union all
select 1, 25, '2012-12-27', 23.8, '2012-12-27', 100.65 union all
select 2, 50, '2012-12-30', 46.7, '2012-12-30', 250.78 union all
select 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 SumUnitsForDistID
from (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq FROM @test)t
output
----------------------------------
DistSumOfUnits SumUnitsForDistID
----------------------------------
351.43 602.21
I think OP wants latter which only will make sense
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
How to sum distinct values
Posted - 01/24/2013 : 11:57:11
