How to count distinct values which meets multiple criteria
The following is the table I have
ID Name CodeID
ID 1 7
ID 1 5
ID 1 7
ID 2 5
ID 2 7
ID 3 5
I wanted to count distinct IDs when the code is 5 , code is 7 and code is 5&7.
Expected result: Count distinct values for the following category
Code is 5 3
Code is 7 2
Code is 5&7 2
Is it wise to use count distinct over partition by here. If not how to achieve this result?
Thanks in advance
Best Answer
If the values for 'code' are known and small, then it's a simple as building a query for each combination of values and UNIONing the results together, but if you wanted a more dynamic solution then it would be more complex
declare @YourTable table (IDName int, CodeID int); insert into @YourTable (IDName, CodeID) select 1,7; insert into @YourTable (IDName, CodeID) select 1,5; insert into @YourTable (IDName, CodeID) select 1,7; insert into @YourTable (IDName, CodeID) select 2,5; insert into @YourTable (IDName, CodeID) select 2,7; insert into @YourTable (IDName, CodeID) select 3,5; with Code5s as (select distinct IDName from @YourTable where CodeID = 5), Code7s as (select distinct IDName from @YourTable where CodeID = 7), Code5n7s as (select distinct IDName from Code5s intersect select distinct IDName from Code7s) select 'Code is 5', count (distinct IDName) from Code5s union all select 'Code is 7', count (distinct IDName) from Code7s union all select 'Code is 5&7', count (distinct IDName) from Code5n7s;
----------- ----------- Code is 5 3 Code is 7 2 Code is 5&7 2 (3 rows affected)
