Report query

I am trying to create a query which should return 4 columns
Facility  |  NoOfActiveApplicants
 |  NoOfArchivedApplican
ts | 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
 |  NoOfArchivedApplican
ts

So far I have the following query, which returns just 2 rows with both
 NoOfActiveApplicants
 &  NoOfArchivedApplican
ts 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.facility
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.facility
order by facility

On Wed, 27 Jul 2005 19:28:42 GMT, c0de w via webservertalk.com wrote:

>
>I am trying to create a query which should return 4 columns
>Facility  |  NoOfActiveApplicants
 |  NoOfArchivedApplican
ts | TotalApplicants
>I would be glad to even have just 3 columns since, the Total can be compute
d
>in the display table (ColdFusion interface)
>
>Facility  |  NoOfActiveApplicants
 |  NoOfArchivedApplican
ts
>
>So far I have the following query, which returns just 2 rows with both
> NoOfActiveApplicants
 &  NoOfArchivedApplican
ts 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.facility
>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.facility
>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
,  NoOfArchivedApplican
ts,
 NoOfActiveApplicants
 +  NoOfArchivedApplican
ts 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
 NoOfArchivedApplican
ts
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