February 6, 2003 at 11:42 pm
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
February 7, 2003 at 1:11 pm
How about
select distinct candidateid,skill,count(*)
from table
group by candidateid,skill
order count(*) desc
February 10, 2003 at 11:38 am
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