How to SUM duplicate row in CASE statement calculation

user_11682035 0
Viewed 716

I have a case statement that calculates a score by a flag per item but assigned to the user of the item.

Its a count of compliant/non compliant items, my problem is if an item has 2 rows in the table, its counting this a 2 Non-Compliant, when it should be 1.

This is the basic case statement:

SELECT 
    user.id,    
    sum( CASE WHEN SCORE_FLAG = 'Y' THEN 1 ELSE 0  END)  AS 'Compliant_Count',
    sum( CASE WHEN SCORE_FLAG = 'N' THEN 1 ELSE 0  END)  AS 'NonCompliant_Count',
    sum( CASE WHEN SCORE_FLAG = 'Y' or SCORE_FLAG = 'N' then 1 ELSE 0 END) AS 'Total_count'
FROM Table
Group By user.id

This is the invalid results below (I added in item column - but this won't be included in my query, as I group by user id only).

usera   item1   0   1   1
usera   item2   0   2   2--
usera   item3   0   1   1
usera   item4   0   1   1

This user has 4 items, but item 2 has 2 rows in the table so is showing a Non-Compliant of 2, when I want it to be 1.

How can I get this to pull back 1 row using the case statement. I have to group the results by user, and not item, as the data is loaded into another table that only contains the user.

Item 2 should only have 1 non compliant count and 1 total count

But when I am grouping the data it is by user, so it should look like this:

Usera   0   4   4

Usera should have a count of 4 non-compliant items, but currently I get a count of 5, as one of them items has 2 rows

duplicates count group-by sql-server sql
2 Answers

Use a COUNT(DISTINCT) instead of a sum

e.g.

SELECT COUNT(DISTINCT CASE WHEN SCORE_FLAG = 'Y' THEN item ELSE NULL END)
user_136363760

I suspect that you want distinct:

SELECT 
    user_id,    
    COALESCE(COUNT(DISTINCT CASE WHEN score_flag = 'Y' THEN item END), 0) AS Compliant_Count,
    COALESCE(COUNT(DISTINCT CASE WHEN score_flag = 'N' THEN item END), 0)  AS NonCompliant_Count,
    COUNT(DISTINCT CASE WHEN score_flag IN ('Y', 'N') THEN item END) AS Total_count
FROM mytable
GROUP BY user_id

Notes:

  • there is no user table alias defined in the query, so user.id is invalid. I renamed it to user_id
  • do not use single quotes for column aliases - single quotes should used for string literals only
  • If Y and N are the only possible values, the last expression can be simplified as just COUNT(DISTINCT item)
user_106767160
Related