I'm having trouble coming up with a value for a cell in SSRS, which should be a sum of distinct values. I have a SSRS report that looks similar to the below screenshot:
I'm having trouble getting the value in red ($11.25). I basically need to sum the Ship Cost, based on distinct Tracking #s. So there are two distinct tracking #s, one with a Ship Cost of $5.25 and the other $6.00, so the total displayed in red should be $11.25. But I cannot achieve this in SSRS and can't figure it out in the SQL query either.
I'm thinking a subquery like (and I know the below is not valid SQL):
(SELECT SUM([Ship Cost]) WHERE [Tracking #] IS DISTINCT) AS [Ship Cost]
But I don't know how to write it.
3 Answers
Get the distinct list first...
SELECT SUM(SQ.COST)
FROM
(SELECT DISTINCT [Tracking #] as TRACK,[Ship Cost] as COST FROM YourTable) SQ
You can do the following:
SELECT SUM(distinct [Ship Cost]) . . .
But, I don't recommend this. You could have two items with the same cost and only one would be counted.
The better way is to select one value for each Tracking #, using the row_number() function:
select SUM(case when seqnum = 1 then [Ship Cost] end)
from (select t.*,
row_number() over (partition by [Order #], [Tracking #]
order by (select NULL)
) as seqnum
. . .
) t
try something like
select sum(shipcost) from (select distinct tracking#, shipcost from table)
cheers
