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

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 Fri Jun 12 1998 - 00:00:00 CDT