Report query

I am trying to create a query which should return 4 columns
Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants
I would be glad to even have just 3 columns since, the Total can be computed
in the display table (ColdFusion interface)

Facility | NoOfActiveApplicants | NoOfArchivedApplicants

So far I have the following query, which returns just 2 rows with both
NoOfActiveApplicants & NoOfArchivedApplicants under the same column.
Am stuck here, any help is apprecialted. Thanks in advance!

select
NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
, Facility = case when c.facility is null then c.JobDBFacilityName else c.
facility end
from tblapplicant a, tblJobDB b, tblfacilities c
where a.jobid = b.jobid
and b.facility = c.facilityid
group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
union all
select
NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
, Facility = case when c.facility is null then c.JobDBFacilityName else c.
facility end
from tblArchiveapplicant a, tblArchiveJob b, tblfacilities c
where a.JobVacancyNumber = b.JobVacancyNumber
and b.facility = c.facilityid
group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
order by facilityOn Wed, 27 Jul 2005 19:28:42 GMT, c0de w via SQLMonster.com wrote:

>I am trying to create a query which should return 4 columns
>Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants
>I would be glad to even have just 3 columns since, the Total can be computed
>in the display table (ColdFusion interface)
>Facility | NoOfActiveApplicants | NoOfArchivedApplicants
>So far I have the following query, which returns just 2 rows with both
>NoOfActiveApplicants & NoOfArchivedApplicants under the same column.
>Am stuck here, any help is apprecialted. Thanks in advance!
>select
> NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
>, Facility = case when c.facility is null then c.JobDBFacilityName else c.
>facility end
> from tblapplicant a, tblJobDB b, tblfacilities c
>where a.jobid = b.jobid
> and b.facility = c.facilityid
>group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
>union all
>select
> NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
>, Facility = case when c.facility is null then c.JobDBFacilityName else c.
>facility end
> from tblArchiveapplicant a, tblArchiveJob b, tblfacilities c
>where a.JobVacancyNumber = b.JobVacancyNumber
> and b.facility = c.facilityid
>group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
>order by facility

Hi c0de,

Hard to say without knowing anything about structure of the tables in
your database (see www.aspfaq.com/5006 for a better way to ask questions
in these groups). But you might see if the following helps you:

SELECT Facility, NoOfActiveApplicants, NoOfArchivedApplicants,
NoOfActiveApplicants + NoOfArchivedApplicants AS
TotalApplicants
FROM (SELECT COALESCE(facility, JobDBFacilityName) AS Facility,
(SELECT COUNT(*)
FROM tblapplicant AS a
INNER JOIN tblJobDB AS b
ON a.jobid = b.jobid
WHERE b.facility = c.facilityid) AS
NoOfActiveApplicants,
(SELECT COUNT(*)
FROM tblArchiveapplicant AS a
INNER JOIN tblArchiveJob AS b
ON a.JobVacancyNumber = b.JobVacancyNumber
WHERE b.facility = c.facilityid) AS
NoOfArchivedApplicants
FROM tblfacilities AS c) AS der
ORDER BY Facility

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi c0de ,
As Hugo correctly pointed out (see www.aspfaq.com/5006 for a better
way to ask questions in these groups). As a friendly advice please do
post DDL,DML as it become easier for others to test their queries .

All I can see you wish to prepare a report which can be done using
corelated subquery (I can be wrong because I am not having data with me
to check the correctness of my query)

Select F.facilityid,
(select count(*) from tblapplicant a, tblJobDB b where a.jobid =
b.jobid and b.facility=F.facilityid),
(select count(*) from tblArchiveapplicant a, tblArchiveJob b where
a.JobVacancyNumber = b.JobVacancyNumber and b.facility=F.facilityid)
from tblfacilities F

May this query solve your problem. As an another advice please use same
column name in all tables that are to be linked (related) .

With warm regards
Jatinder Singh