Post reply

distinct output require

  • February 6, 2003 at 11:42 pm

    #58444

    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'

    3 '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

  • February 7, 2003 at 2:22 am

    #448337

    select count(distinct a.candidateid) as 'candidates'

    from skillset a

    inner join skillset b on b.candidateid = a.candidateid and b.skill = 'oracle'

    where a.skill = 'xml'

    select count(distinct candidateid) as 'candidates'

    from skillset

    where skill = 'oracle'

    Edited by - davidburrows on 02/07/2003 02:23:09 AM

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 7, 2003 at 4:20 am

    #448350

    Hi david,

    thanks for your asnwer.I am looking for universal query.It should satisfy any no of skill set.In query i can pass any no of skill.if u have any idea,Please suggest.

    thanks

    vijay

  • February 7, 2003 at 4:51 am

    #448353

    Assume you pass skill list and count of skills then try

    declare @list varchar(100),@kount int

    set @list = 'xml,oracle'

    set @kount = 2

    select count(*) from (select candidateid,count(*) as 'skillct' from skillset where @list like '%'+skill+'%' group by candidateid having count(*) = @kount) a

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 7, 2003 at 5:21 am

    #448360

    Hi david,

    It works.

    thanks

    vijay

  • February 7, 2003 at 5:22 am

    #448361

    Hi david,

    It works.

    thanks

    vijay

  • February 7, 2003 at 5:37 am

    #448364

    It will not work if table has duplicate row.

    for example ,if candidate 1 has xml skill twice or more times in a table.any suggestion for this.because it is possible in our system.

    thanks

    vijay

  • February 10, 2003 at 2:36 am

    #448478

    Need to add another subquery

    select count(*) from (select candidateid,count(*) as 'skillct'

    from (select distinct candidateid,skill from #skillset) a

    where @list like '%'+skill+'%' group by candidateid having count(*) = @kount) b

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 10, 2003 at 4:18 am

    #448497

    Hi david,

    thanks.

    vijay

  • February 10, 2003 at 11:44 am

    #448559

    Here are a few 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 10 posts - 1 through 9 (of 9 total)

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