Build SQL statement combining 1 table 3 ways

I need help building a SQL statement to combine the following selects on a table:

[code]
select to_char(a.order_datetime, 'MM') dt, a.case_coordinator doc, a.species_id, count(a.accession)
from accession a
where lab_sections_involved like '%P%'
and lab_sections_involved not like '%N%'
and a.order_datetime between to_date('01-JAN-2002 06:00', 'dd-mon-yyyy hh24:mi')
and to_date('31-DEC-2002 17:00', 'dd-mon-yyyy hh24:mi')
group by to_char(a.order_datetime, 'MM'), a.case_coordinator, a.species_id
---------
select to_char(a.order_datetime, 'MM') dt, a.case_coordinator doc, a.species_id, count(a.accession)
from accession a
where lab_sections_involved like '%N%'
and lab_sections_involved not like '%P%'
and a.order_datetime between to_date('01-JAN-2002 06:00', 'dd-mon-yyyy hh24:mi')
and to_date('31-DEC-2002 17:00', 'dd-mon-yyyy hh24:mi')
group by to_char(a.order_datetime, 'MM'), a.case_coordinator, a.species_id
----------
select to_char(a.order_datetime, 'MM') dt, a.case_coordinator doc, a.species_id, count(a.accession)
from accession a
where lab_sections_involved like '%P%'
and lab_sections_involved like '%N%'
and a.order_datetime between to_date('01-JAN-2002 06:00', 'dd-mon-yyyy hh24:mi')
and to_date('31-DEC-2002 17:00', 'dd-mon-yyyy hh24:mi')
group by to_char(a.order_datetime, 'MM'), a.case_coordinator, a.species_id
[/code]

Basically, I'm trying to find the counts for a series of lab tests, broken down by month, by case coordinator (the veterinarian in charge), and by animal species. For each month/coordinator/vet, I need to know the cases that were N cases but were not P cases, the number that were P but not N cases, and the number that were both N and P cases.

The above three queries should work within 1 query, but I can't figure out how to combine them correctly.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories