SUM values only for distinct ID's

Question

Post reply

SUM values only for distinct ID's

Bahmudales

SSC Enthusiast

Points: 129
More actions
September 16, 2013 at 4:32 am

#277982

Hello,
I have the following report:
ID Time
311793 3:12
312184 3:13
312184 3:13
312373 3:04
Is there a way to sum 'Time' values only for distinct ID's?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tee Time Hall of Fame Points: 3745 More actions · Answer 1

What are you expecting the results of this to look like?

Bahmudales SSC Enthusiast Points: 129 More actions · Answer 2

Without distinct the result is:

311793 3:12

312184 3:13

312373 3:04

Total: 12:42

.. but I want only values for distinct ID's to be added:

311793 3:12

312184 3:13

312373 3:04

Total 9:29

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 3

----------

Without distinct the result is:

311793 3:12

312184 3:13

312373 3:04

Total: 12:42

.. but I want only values for distinct ID's to be added:

311793 3:12

312184 3:13

312373 3:04

Total 9:29

----------

Normally you would run some kind of distinct function on the dataset before passing to SSRS, as this would return the report you require.

If you needed all values to be shown but not to be summed then use something like below. Both will return the original rows (4), but allow the second and subsequent values to be converted to 0.

declare @SourceDatatable (OrderNumber int, Value money, Code char(1))

insert into @SourceData

SELECT 311793 AS OrderNumber, 3.12 AS Value, 'A' AS Code

UNION

SELECT 312184 AS OrderNumber, 3.13 AS Value, 'A' AS Code

UNION

SELECT 312184 AS OrderNumber, 3.13 AS Value, 'D' AS Code

UNION

SELECT 312373 AS OrderNumber, 3.04 AS Value, 'A' AS Code

select * from @SourceData;

--- Option 1 : SQL 2005+

with ComparedValues as (

select * , row_number() over (partition by OrderNumber order by Code) as RN

from @SourceData

)

select OrderNumber, Value, Code, case when RN=1 then Value else 0 end as ConvertedValue

from ComparedValues;

--- Option 1 : SQL 2012+

select OrderNumber, Value, Code,

case when OrderNumber = lag(OrderNumber,1,NULL) over

(partition by OrderNumber order by Code)

then 0 else Value end as ConvertedValue

from @SourceData;

Fitz

Tee Time Hall of Fame Points: 3745 More actions · Answer 4

Agree with Fitz, SELECT DISTINCT should get you what you are looking for.