Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to sum distinct values

Author  Topic 

V.V.
Starting Member

32 Posts

Posted - 2013-01-24 : 11:57:11
I have a table like this:

ID, CB, Date_CB, CN, Date_CN, Units
1 25 2012-12-12 23.8 2012-12-12 100.65
1 25 2012-12-27 23.8 2012-12-27 100.65
2 50 2012-12-30 46.7 2012-12-30 250.78

The idea is I want to sum Units for each distinct ID. How to?
Because if I make sum(Units) it returns me double the amount of Units and I want to sum distinct Units for distinct ID.

Sorry if I wasn't clear in what I want.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-24 : 12:01:47
You can actually do distinct sum! SUM(DISTINCT Units) will give you 100.65 for ID = 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 00:50:00
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/

Go to Top of Page
   

- Advertisement -