distinct sum for an expression

  • I have a report that displays the sum of a particular field like this:

    =SUM(iif(Fields!isFinished.Value = 1, Fields!isFinished.Value, 0))

    Sometimes it works, but sometimes I get the wrong totals because it's counting one particular field more than once because that field happens to contain the same Id more than once.

    Here is some sample data that shows the results that I'm dealing with:

    SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished

    UNION ALL SELECT 'Jimmy','12345',0

    UNION ALL SELECT 'Jimmy','789',1

    UNION ALL SELECT 'Alex','12345',1

    UNION ALL SELECT 'Alex','12345',1

    UNION ALL SELECT 'Alex','12345',0

    UNION ALL SELECT 'Kate','53274',1

    UNION ALL SELECT 'Kate','789',1

    UNION ALL SELECT 'Kate','83752',1;

    I only need the report to SUM the row on each questionId once. So a sample report would just have totals like this:

    only count each weapondId ONCE

    Totals:

    Jimmy = 2

    Alex = 1

    Kate = 3

    After some googling, I tried playing around with ROW_NUMBER and RANK in my SQL query, but that didn't solve my problem.

    Is there a way to do this in SSRS or even the SQL?

    Thanks!

  • Should your Output have Alex = 2?

  • sharonsql2013 (2/1/2016)


    Should your Output have Alex = 2?

    No because it's the same weaponId. I only want to count isFinished once for each weaponId.

    Thanks

  • You can do it in SQL using a subquery, turn it into a distinct list there and then summarise.

    WITH CTE AS (

    SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished

    UNION ALL SELECT 'Jimmy','12345',0

    UNION ALL SELECT 'Jimmy','789',1

    UNION ALL SELECT 'Alex','12345',1

    UNION ALL SELECT 'Alex','12345',1

    UNION ALL SELECT 'Alex','12345',0

    UNION ALL SELECT 'Kate','53274',1

    UNION ALL SELECT 'Kate','789',1

    UNION ALL SELECT 'Kate','83752',1

    )

    SELECT Person, SUM(isFinished)

    FROM (SELECT DISTINCT Person, weaponId, isFinished

    FROM CTE) t

    GROUP BY Person

  • Thanks!

    I was wondering, do you know if it would be possible to combine both queries so that I get something like this?

    SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished, 2 As Finished_Total

    UNION ALL SELECT 'Jimmy','12345',0, 2 As Finished_Total

    UNION ALL SELECT 'Jimmy','789',1, 2 As Finished_Total

    UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total

    UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total

    UNION ALL SELECT 'Alex','12345',0, 1 As Finished_Total

    UNION ALL SELECT 'Kate','53274',1, 3 As Finished_Total

    UNION ALL SELECT 'Kate','789',1, 3 As Finished_Total

    UNION ALL SELECT 'Kate','83752',1, 3 As Finished_Total;

    I realize that the 'Finished_Total' column would repeat, but I need that figure in my final SSRS report.

    Thanks!

  • I feel like there is probably a more elegant way of doing it, but this was what I came up with. Just joining the summarised dataset to the original dataset.

    ;WITH CTE AS (

    SELECT 'Jimmy' AS Person,'12345' As weaponId,1 AS isFinished, 2 As Finished_Total

    UNION ALL SELECT 'Jimmy','12345',0, 2 As Finished_Total

    UNION ALL SELECT 'Jimmy','789',1, 2 As Finished_Total

    UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total

    UNION ALL SELECT 'Alex','12345',1, 1 As Finished_Total

    UNION ALL SELECT 'Alex','12345',0, 1 As Finished_Total

    UNION ALL SELECT 'Kate','53274',1, 3 As Finished_Total

    UNION ALL SELECT 'Kate','789',1, 3 As Finished_Total

    UNION ALL SELECT 'Kate','83752',1, 3 As Finished_Total

    ), CTE_totals AS (

    SELECT Person, SUM(isFinished) AS Finished_Total

    FROM (SELECT DISTINCT Person, weaponId, isFinished

    FROM CTE

    ) t

    GROUP BY Person

    )

    SELECT

    CTE.*, CTE_totals.Finished_Total

    FROM

    CTE INNER JOIN CTE_totals ON CTE.Person = CTE_totals.Person

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

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