Trying to sum distinct values SQL

sql reporting-services distinct-values
kyle_13 picture kyle_13 · Aug 13, 2013 · Viewed 65.2k times · Source

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:

enter image description here

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.

Answer

Declan_K picture Declan_K · Aug 13, 2013

Get the distinct list first...

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

Related questions

How do I format date and time on ssrs report?

on SSRS report I need to show todays date and current time i tried this =FormatDateTime(Now,"MM/dd/yyyy hh:mm tt") but this is not working for me giving an error. Anyone please help me for expression ? I …

Read More
Passing multiple values for a single parameter in Reporting Services

I have several Multi-Select parameters in my report. I am trying to find a way to pass in multiple values for a single parameter in the web query string? If I pass in a single value, it works fine. The …

Read More
how to remove time from datetime

The field DATE in the database has the following format: 2012-11-12 00:00:00 I would like to remove the time from the date and return the date like this: 11/12/2012

Read More