oracle sql query question(grouping by 2 columns)

Question

I have a table called testgroup in my database, which is like following:

I                      J                      
---------------------- ---------------------- 
1                      a                      
1                      a                      
2                      a 
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.
For example: with I = 1, there are 2 a in column J, so the third column would be equal 2.

Johan · Accepted Answer · 2011-06-04 11:03:57Z

25

select I, J, count(*) as JinI
FROM atable
GROUP BY I, J

answered Jun 4, 2011 at 11:03

Johan

77.4k28 gold badges204 silver badges346 bronze badges

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

Comments

Farshid Zaker · Accepted Answer · 2011-06-04 11:03:08Z

10

In fact the question is about counting I and J pairs:

select I, J, count(*) from tblName group by I, J

answered Jun 4, 2011 at 11:03

Farshid Zaker

1,9902 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

oracle sql query question(grouping by 2 columns)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related