Menu
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.
971
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
