Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SUM values only for distinct ID's Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 4:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:06 AM
Points: 4, Visits: 38
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?

Post #1494988
Posted Monday, September 16, 2013 6:36 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:09 AM
Points: 532, Visits: 447
What are you expecting the results of this to look like?
Post #1495035
Posted Monday, September 16, 2013 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:06 AM
Points: 4, Visits: 38
Without distinct the result is:
311793 3:12
312184 3:13
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
Post #1495062
Posted Tuesday, September 17, 2013 4:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 3,102, Visits: 1,341
----------
Without distinct the result is:
311793 3:12
312184 3:13
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 @SourceData	table (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
Post #1495423
Posted Tuesday, September 17, 2013 6:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:09 AM
Points: 532, Visits: 447
Agree with Fitz, SELECT DISTINCT should get you what you are looking for.
Post #1495475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse