question

vaishnavi avatar image
vaishnavi asked

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



querysql query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.