SQL Query

Hi,

Following is the table Structure in DB:
id: auto increment integer
name: varchar(256)
person_id: an integer that references a person in a different table
type: two values - 0 or 1

0 means a person w/ type0
1 means a person w/ type1

Following values and (others not mentioned) are possible:
1 XXX 1 0
2 XXX 1 1
3 YYY 2 0
4 ZZZ 3 1
5 AAA 2 0

The following are what I like to find out - number of persons who
a) are just type 0
b) are just type 1
c) are both type 0 and type 1

Initially, I wrote a simple query to answer a)
select count(person_id) from TABLE where type = 0 AND person_id NOT IN (select person_id from TABLE where type = 1 group by person_id) group by person_id;

But, it is too slow to compute - almost ran for 10 hours and still not completed.
There is a index for each column - id, person_id, type

Please provide new SQL queries that is faster and does the above job.

Thanks!
Purdue_PeteAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
derekkrommConnect With a Mentor Commented:
try these:
-- 0 only
select
	t1.person_id, count(*) cnt
from
	tbl t1
	left outer join tbl t2
		on	t1.person_id = t2.person_id
			and t2.type = 1
where
	t1.type = 0
	and t2.type is null
group by
	t1.person_id

-- 1 only
select
	t1.person_id, count(*) cnt
from
	tbl t1
	left outer join tbl t2
		on	t1.person_id = t2.person_id
			and t2.type = 0
where
	t1.type = 1
	and t2.type is null
group by
	t1.person_id


-- 0 and 1
select
	t1.person_id, count(*) cnt
from
	tbl t1
	inner join tbl t2
		on	t1.person_id = t2.person_id
			and t1.type = 0
			and t2.type = 1
group by
	t1.person_id

Open in new window

0
 
kraivenConnect With a Mentor Commented:
Maybe I'm misunderstanding something but from your sample all you appear to need is:

select count(person_id) from TABLE where type = 0

Open in new window


Why the grouping in the WHERE clause?
0
 
derekkrommConnect With a Mentor Commented:
he wants only 0 and only 1

doing count where type=0 would return anyone that is both 0-only and 0-and-1
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
to find the persons that are only 0:
select name from yourtable group by name having min(type) = 0 and max(type) = 0 

Open in new window


to find the persons that are only 1:
select name from yourtable group by name having min(type) = 1 and max(type) = 1 

Open in new window


to find the persons that are both 0 and 1:
select name from yourtable group by name having min(type) = 0 and max(type) = 1 

Open in new window


from there, you can subselect to count those ...
0
 
ccwill88Connect With a Mentor Commented:
use exists to query:
a) are just type 0
select count(distinct person_id) from TABLE a where type = 0 AND person_id NOT EXISTS  (select * from TABLE b where type = 1  where a.person_id=b.person_id)  

Open in new window

b) are just type 1
select count(distinct person_id) from TABLE a where type = 1 AND person_id NOT EXISTS  (select * from TABLE b where type = 0  where a.person_id=b.person_id)  

Open in new window

c) are both type 0 and type 1
select count(distinct person_id) from TABLE a,TABLE b where  a.person_id=b.person_id
and a.type=0 and b.type=1  

Open in new window

0
All Courses

From novice to tech pro — start learning today.