SQL to calculate distinct count for a desired table.

Question

id1     id2    year State Gender    
====  ====== ====== ===== =======    
1       A      2008    ca      M    
1       B      2008    ca      M    
3       A      2009    ny      F   
3       A      2008    ny      F     
4       A      2009    tx      F

This is the table i have, I need to find the total No of distinct Count in this table. I need to consider id1 and id2 to find the Unique Count. so the result of the count should be grouped according to the state. considering the above example.

i need to get the result as
ca - count : 2  and for ny it should be : 1 as  3 A is repeated twice.

the query i came up is that:

select state,gender,year,count(distinct id1,id2) from table1 
group by state,gender,year

in this query i couldn't calculate a distinct count of id1,id2. how should i modify my query to get the desired result. any help would be appreciated.

Answer 1

If you are using sql server 2005 or upwards then you can use ranking functions. For example,

WITH temp AS(
    SELECT
        state,gender,[year],
        rank() OVER (ORDER BY id1,id2) AS rank1
    FROM table1
)
SELECT
    state,gender,[year],
    COUNT(DISTINCT rank1)
FROM 
    temp
GROUP BY state,gender, [year]

Other crud way would be to combine id1 and id2 and take count on that combination

select
    state, gender, [year],
    count (distinct(cast(id1 as varchar(10)) + id2))
from
    try1
group by state, gender, [year]

Answer 2

Try using a sub select:

select state, gender, year,
    count(select count(*) as total from table1
          where state = t.state and gender = t.gender and year = t.year
          group by id1, id2)
    from table1 t
group by state, gender, year

Answer 3

I am no expert, but I hacked up this, which seems to work.

select count(1) from stack where state='ny' group by id1, id2;
select @@ROWCOUNT;

I'm happy to have it critiqued. I'm here to learn!

Answer 4

[Updated]

Try the following:

select state, count(distinct cast(id1 as varchar) + id2) cnt from table1 
group by state

Answer 5

SELECT [state],
  (SELECT COUNT(*) FROM (
    SELECT DISTINCT id1, id2 FROM table1 WHERE [state] = t.[state]
  ) z) AS cnt
FROM table1 t
GROUP BY [state]