Howdy, Stranger!

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

Categories

Build SQL statement combining 1 table 3 ways

Ara_camAra_cam Member Posts: 37
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.