CODEWITHSUNDEEP
sqlsql-servert-sql
jero
jero

Reputation: 543

SQL to calculate distinct count for a desired table

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.

Upvotes: 0

Views: 621

Answers (5)

VladV
VladV

Reputation: 10349

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

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

[Updated]

Try the following:

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

Upvotes: 0

Synesso
Synesso

Reputation: 38978

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!

Upvotes: 0

krock
krock

Reputation: 29619

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

Upvotes: 0

VinayC
VinayC

Reputation: 49195

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]

Upvotes: 2

Related Questions