Count and multiple Group By
-
I am trying to get a count of the distinct number of rows in my table when I group by two columns. Here is a sample of what I tried.
declare @test-2 table (col_1 int, col_2 int)
insert @test-2
values(1,2)
insert @test-2
values(1,2)
insert @test-2
values(1,3)
select count(*)
from @test
group by col_1, col_2
The result is:
-----------
2
1
In this example I was looking to get a count of 2. After thinking about it, I understand why I got two rows but I don't understand why one of the rows is 1 and the other is 2. Can someone explain why? Also, is there some way I can calculate the distinct number of rows in the above example?
Thanks,
-Kevin Williams
-
use the following query to get the desired output
declare
@test-2 table (col_1 int, col_2 int)
insert
values
(1,2)
insert
values
(1,2)
insert
values
(1,3)
select
distinct col_1,col_2
from
group
by col_1, col_2
select
count(*) from
(
select
distinct col_1,col_2
from
group
by col_1, col_2
)
A
-- Please go thru the BOL to get more info on Group by
-
select count(*)
from @test
group by col_1, col_2
Statement is same as:
select count(*),col_1,col_2
from @test
group by col_1, col_2
So its giving you the count of combinations of col_1 and col_2. Hope this clarifies your question.
Thanks
Sreejith
-
Now I get it! Thanks for helping.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply