Count distinct values based on criteria in multiple columns

Question

I have a table like this from which I want to count of recurrences per Username. I would like to count how many distinct Type are there with Amount <> 0 per user:

+----------+-------------+--------+
| Username |    Type     | Amount |
+----------+-------------+--------+
| abc      | New         |      1 |
| abc      | New         |      1 |
| abc      | Old         |      1 |
| def      | New         |      1 |
| def      | Old         |      0 |
| def      | Refurbished |      1 |
| ijk      | New         |      1 |
| ijk      | Old         |      2 |
| ijk      | Refurbished |      3 |
+----------+-------------+--------+

My result should look something like this:

+----------+---------------+
| Username | DistinctCount |
+----------+---------------+
| abc      |             2 |
| def      |             2 |
| ijk      |             3 |
+----------+---------------+

..where [Username] abc has 2 distinct [Type] with [Amount] not equal to 0.

I have tried SELECT DISTINCT Type, Username, SUM(CASE WHEN Amount <> 0 THEN 1 ELSE 0 END) FROM tblBase GROUP BY Type, Username but it's not yielding the desired result.

Community · Accepted Answer · 2021-01-18 12:30:34Z

4

Use COUNT DISTINCT with CASE:

SELECT
    Username,
    DistinctCount = COUNT(DISTINCT CASE WHEN Amount <> 0 THEN Type END)
FROM #tbl
GROUP BY Username;

The difference with using WHERE clause is that, this will return rows with 0 DistinctCount, while the WHERE clause will not.

DEMO

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered Mar 10, 2016 at 5:14

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shanka Over a year ago

Thanks! This is perfect!

Matt G Over a year ago

It's also the worst solution, isn't it? It's inefficient because you're considering all rows, even when zero. I bet this would run worse in the majority of use cases...

Felix Pamittan Over a year ago

@MattG, maybe. But this is completely different from yours and Backtrack's solution using the WHERE condition. If he intend to exclude 0 rows, then the WHERE clause would be correct and this one's wrong. Otherwise, it's the only correct solution here.

backtrack · Accepted Answer · 2016-03-10 05:13:29Z

3

 SELECT Username, COUNT(DISTINCT Type) AS DistinctCount
    FROM tblBase  WHERE Amount <> 0 
    GROUP BY Username

I would say, use Amount <> 0 in Where and group by using username. And use distinct to get the count

answered Mar 10, 2016 at 5:13

backtrack

8,1545 gold badges56 silver badges103 bronze badges

Comments

Matt G · Accepted Answer · 2016-03-10 05:18:04Z

3

According to your output, you want one row per username. That means you want to group by username. You want to count the number of distinct types by username, and there's an aggregate function for that. Try this:

SELECT    username, 
          COUNT(DISTINCT Type) as counted 
FROM      tblBase 
WHERE     Amount <> 0 
GROUP BY  username

edited Mar 10, 2016 at 5:18

answered Mar 10, 2016 at 5:12

Matt G

3463 silver badges11 bronze badges

Collectives™ on Stack Overflow

Count distinct values based on criteria in multiple columns

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related