SQL Count number of column based on another column

Question

Simple question here but I don't have any data to test with so I'm not sure if it is right.

Let's say I have a table that looks something like this:

+---------+---------+
| ColumnA | ColumnB |
+---------+---------+
|       1 | a       |
|       2 | a       |
|       3 | a       |
|       3 | b       |
|       4 | a       |
|       5 | a       |
|       6 | a       |
|       6 | b       |
|       6 | c       |
|       7 | a       |
+---------+---------+

I want to count the number of distinct items in column b for each column a.

So the result table would look like this:

+---------+---------+
| ColumnA | Count   |
+---------+---------+
|       1 |       1 |
|       2 |       1 |
|       3 |       2 |
|       4 |       1 |
|       5 |       1 |
|       6 |       3 |
|       7 |       1 |
+---------+---------+

How would I do this?

I've been trying something like this:

dense_rank() over (partition by ColumnA order by ColumnB) count

Sebastian Brosch · Accepted Answer · 2020-03-27 21:40:55Z

1

You can use GROUP BY and COUNT with DISTINCT:

SELECT ColumnA, COUNT(DISTINCT ColumnB) AS [Count]
FROM MyTable 
GROUP BY ColumnA

demo on dbfiddle.uk

edited Mar 27, 2020 at 21:40

answered Mar 27, 2020 at 21:22

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

SQL Count number of column based on another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related