Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

oracle sql query question(grouping by 2 columns)

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.

like image 971
csuo Avatar asked Jun 04 '11 11:06

csuo


People also ask

Can we GROUP BY 2 columns in SQL?

SELECT Statement: The GROUP BY Clause in SQLA GROUP BY clause can contain two or more columns—or, in other words, a grouping can consist of two or more columns.

How does GROUP BY work with multiple columns in SQL?

We can group the resultset in SQL on multiple column values. When we define the grouping criteria on more than one column, all the records having the same value for the columns defined in the group by clause are collectively represented using a single record in the query output.

How do you SELECT multiple columns in GROUP BY?

All i have to do is: create WITH CTE_Name subquery with your GroupBy column and COUNT condition. select all(or whatever you want to display) from value table and the total from the CTE. INNER JOIN with CTE on the ID(primary key or unique constraint) column.


2 Answers

select I, J, count(*) as JinI
FROM atable
GROUP BY I, J
like image 146
Johan Avatar answered Oct 05 '22 23:10

Johan


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

select I, J, count(*) from tblName group by I, J
like image 22
Farshid Zaker Avatar answered Oct 05 '22 22:10

Farshid Zaker