Trying to sum distinct values SQL

Question

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.

Patrology

Answer 1

Get the distinct list first...

SELECT SUM(SQ.COST)
FROM
(SELECT DISTINCT [Tracking #] as TRACK,[Ship Cost] as COST FROM YourTable) SQ

Bouse

Answer 2

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

Michell

Hi @Gordon Linoff, you seem to have a good solution. I couldn't quite get the query you gave to work properly, but I did something similar using the ROW_NUMBER function. I now have a new output on my report. The values in red in the screenshot are calculated using the following Expression in SSRS: =SUM(IIF(Fields!RowNumber.Value = 1, Fields!WEIGHT.Value, 0)) however, it gives an error for those orders with more than 1 item. Still not making sense to me. Do you know how the Expression can be modified so the report output is correct? – Patrology

@Patrology . . . I think you should ask another question providing information about sample data and expected results. – Michell

Thanks @Gordon Linoff, that's what I ended up doing, got the solution here #18236873. Was a data conversion issue on the report. – Patrology

I wonder if there is any advantage to this solution vs what @Bouse posted. – Soliz

@GordonLinoff @DavidFaivre I actually think this should be the accepted answer. The main advantage I see is that it is fairly independent of the rest of the query. That is, @Declan_K's solution requires adding DISTINCT to the entire subquery so it only returns a single record for each tracking number, but that isn't feasible if you want to include data from joined tables that would produce duplicate rows. This solution allows the original query structure to remain intact. +1 – Bodnar

@SeantheBean I totally agree, in fact this solution solved my problem while others didnt – Crypt

Answer 3

try something like

select sum(shipcost) from
(select distinct tracking#, shipcost from table)

cheers

Balduin

Answer 4

If you have an IDENTITY column, you can use this trick:

SELECT (SUM(DISTINCT [Ship Cost] + ID) - SUM(DISTINCT ID)) AS [Ship Cost] FROM ...

I use this trick to avoid SUM duplicate rows in a complex query with lots of JOINs

Pejsach

This still might not work because the sum of A + B can be the same as C + D and will be ignored in the distinct – Flu