zhanzezhu zhanzezhu - 6 months ago 11
SQL Question

SQL:How to count record which distinct?

The data source:

id idx num
1 1 1
2 1 2
3 2 3
4 2 3
5 3 4
6 3 4
7 3 5


I need count record which the same idx and count num's type.

I hope result:

idx num
1 2
2 1
3 2

Answer

What you want is a DISTINCT count:

SELECT idx, COUNT(DISTINCT num) AS num
FROM mytable
GROUP BY idx