distinct output require

  • I have one table skillset,which has two column, candidateid and skill.

    data stored in this table are

    candidateid skill

    1 'xml'

    1 'oracle'

    1 'j2ee'

    2 'xml'

    2 'oracle'

    suppose i want to know how many distinct candidateid know 'xml' and 'oracle'

    or how many distinct candidateid know 'oracle'.how i will write this query.

    thanks

    vijay

  • How about

    select distinct candidateid,skill,count(*)

    from table

    group by candidateid,skill

    order count(*) desc

  • Here are a few different examples that might help you out.

    set nocount on

    create table skill(candidateid int, skill varchar(10))

    insert into skill values(1, 'xml')

    insert into skill values(1, 'oracle')

    insert into skill values(1, 'j2ee')

    insert into skill values(1, 'oracle')

    insert into skill values(1, 'SQL Server')

    insert into skill values(2, 'xml')

    insert into skill values(2, 'oracle')

    insert into skill values(3, 'oracle')

    insert into skill values(4, 'SQL Server')

    -- Q1:suppose i want to know how many distinct candidateid know 'xml' and 'oracle'?

    -- note sub-selects gets rid of dups should the exist in you data. Also if more than two skill this gets even

    -- complicated.

    select 'Number of CandidateId''s that have ''Oracle'' and ''xml'' = ',count(*)

    from (select distinct candidateid, skill from skill) a

    where candidateid = (select candidateid from (select distinct candidateid, skill from skill) b where

    b.skill = 'xml'

    and

    a.candidateid = b.candidateid)

    and skill = 'oracle'

    -- here is a method that allows you to easily add more than two skills if needed

    select 'Number of CandidateId''s that have ''Oracle'' and ''xml'' = ',count(*) from (select candidateid from

    (select distinct candidateid, skill from skill

    where skill = 'xml' or skill = 'oracle') a

    group by candidateid

    having count(*) > 1) b

    -- here is a example of having skill 'oracle', 'xml', and 'SQL Server'

    select 'Number of CandidateId''s that have ''Oracle'', ''xml'' and ''SQL Server'' = ',count(*) from (select candidateid from

    (select distinct candidateid, skill from skill

    where skill = 'xml' or skill = 'oracle' or skill = 'SQL Server') a

    group by candidateid

    having count(*) > 2) b

    -- Q2: how many distinct candidateid know 'oracle'?

    select 'Number of CandidateId''s that have ''Oracle'' = ', count(distinct candidateid)

    from skill

    where skill = 'oracle'

    drop table skill

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply