I have a table called testgroup
in my database, which is like following:
I J
---------------------- ----------------------
1 a
1 a
2 a开发者_如何学Python
1 b
1 c
2 b
3 d
2 b
2 b
3 d
Now, I want the result as below:
I J COUNT(J) in I
---------------------- ---------------------- ----------------------
1 a 2
2 a 1
1 b 1
1 c 1
2 b 3
3 d 2
...where count(j) in I
is the number of each J related to the I.
I = 1
, there are 2 a
in column J, so the third column would be equal 2.select I, J, count(*) as JinI
FROM atable
GROUP BY I, J
In fact the question is about counting I and J pairs:
select I, J, count(*) from tblName group by I, J
精彩评论