Re: Problem figuring out subquery/outer join/count...

From: Steve Davis <spd_at_patrol.i-way.co.uk>
Date: Sat, 13 Jun 1998 22:33:45 GMT
Message-ID: <3582ff8d.0@news.i-way.co.uk>

Colin,

select
e.month month,

count(e.empnum) no_of_new_emps,
count(g.serious) no_of serious,
count(g.serious) / count(e.empnum) * 100 percent_serious

from grievances g,
emps e
where g.empnum (+) = e.empnum
and g.serious (+) = 'Yes'
group by e.month

should do it

Note that this assumes that there cannot be more than one serious grievance for a single employee - otherwise the emplyee and grievance will be counted twice.

Hope this helps,
Steve

"Colin Mackenzie" <cmackenz_at_nortel.com> wrote:

>Hi...I have a tricky SQL question (well, at least tricky for me), I'm trying
>to do a query that involves conditional counts from two different tables,
>and these tables are in a zero to many relationship.

>A fictitious example illustrating my problem is a database that has an
>employee table and a grievance table, the employee table has an employee
>number, name, and start month...and the grievance table has an employee
>number and a 'seriousness' flag (for whether the grievance was serious).

>Assuming that changes can't be made to the table layout, I need to be able
>to write a query listing the number of new employees each month, followed by
>the number of new employees that didn't have a serious grievance filed
>against them, then a percent (the actual solution has nothing to do with
>employees, and technically in my case the employee number isn't the primary
>key, but it's the simplest example to explain it)...something like either of
>these queries (neither of which are legal, but they illustrate what I'm
>trying to do):

>select MONTH, count(distinct E.EMPNUM) A,
>GR.BAD B, (B/A*100) PERCENT FROM EMPLOYEES E,
>(select count(*) BAD from GRIEVANCES G where
>G.EMPNUM=E.EMPNUM and G.SERIOUS='Yes') GR
>group by 1;

>or

>select MONTH, count(distinct E.EMPNUM) A,
>count(distinct G.EMPNUM where G.SERIOUS='Yes') B,
>(B/A*100) PERCENT from EMPLOYEES E, GRIEVANCES G
>where G.EMPNUM=E.EMPNUM(+)
>group by 1;

>The end result is to try and have monthly percentages of new employees that
>had grievances filed against them...but I can't figure out how to achieve
>what either of these illegal queries is attempting to do.

>Any ideas would be greatly appreciated...

>Cheers, Colin.
Received on Sat Jun 13 1998 - 17:33:45 CDT