Multiple Select statement using COUNT

Question

I'm trying to combine these two select queries but the count function is confusing me with Oracle:

select person.first_name, COUNT(person.PERSON_ID) as Active
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
and incident.incident_id = 1
AND (incident.dept_id = 111 OR incident.dept_id = 222)
GROUP BY person.first_name;


select person.first_name, COUNT(person.PERSON_ID) as NonActive
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
AND incident.incident_id = 2
AND (incident.dept_id = 111OR incident.dept_id = 222)
GROUP BY person.first_name

I'm trying to return a single result as:

FIRST_NAME ACTIVE NonActive
Bob          5       11
John         3       14

What would be the best (efficient) way of doing this?

Conrad Frix · Accepted Answer · 2010-11-04 21:49:40Z

3

Here are two ways to do it. I pretty sure SUM CASE will be better but you can test it yourself

Using SUM CASE

select person.first_name, 
SUM(case when incident.incident_id =1 THEN 1 ELSE 0 END) as Active,
SUM(case when incident.incident_id =2 THEN 1 ELSE 0 END) AS NonActive,

FROM incident, person
where person.PERSON_ID = incident.OWNER_ID

   AND (incident.dept_id = 111 OR incident.dept_id = 222)
   AND incident.incident_id IN (1,2)

GROUP BY person.first_name;

Using JOIN

SELECT  DISTINCT
    p.First_name,
    Acive.Active,
    NonActive.NonActive
FROM
PERSON p

LEFT JOIN 
(
select person.first_name, COUNT(person.PERSON_ID) as Active
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
and incident.incident_id = 1
AND (incident.dept_id = 111 OR incident.dept_id = 222)
GROUP BY person.first_name;
) Active
ON p.first_name = active.first_name
LEFT JOIN 
(
select person.first_name, COUNT(person.PERSON_ID) as NonActive
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
AND incident.incident_id = 2
AND (incident.dept_id = 111OR incident.dept_id = 222)
GROUP BY person.first_name
) NonActive

ON p.first_name = NonActive.first_name

where Active.First_name is not null
and NonActive.First_name is not null

edited Nov 4, 2010 at 21:49

answered Nov 4, 2010 at 21:05

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

madlan Over a year ago

Great! How do I exlude rows that have 0 values for both columns from the results?

Conrad Frix Over a year ago

@madlan Added AND incident.incident_id IN (1,2) to the where clause for the first one. I think the second one already does this

madlan Over a year ago

The join seems to return multiple results (rows) for the same people?

Conrad Frix Over a year ago

Sorry You'll need to add a DISTINCT

Paul Sonier · Accepted Answer · 2010-11-04 21:05:29Z

0

I believe you want to do your count on the incident_id instead of person_id; that way, you can combine your two queries into one and get the result you want.

answered Nov 4, 2010 at 21:05

Paul Sonier

39.6k3 gold badges81 silver badges118 bronze badges

Collectives™ on Stack Overflow

Multiple Select statement using COUNT

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related