Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL-statement

SQL-statement

From: Ole Hansen <ohahaahr_at_hotmail.com>
Date: 20 Apr 2005 01:50:11 -0700
Message-ID: <3d05b5c1.0504200050.36931b96@posting.google.com>


Hi !

I am a newbie, learning SQL. I have some questions, regarding a script. For simplicity reasons, i have made this example.

I have 2 tables, COMPANY and EMPLOYEE. COMPANY is the name of the company, and EMPLOYEE is the employees working in the company.

They look like this:

COMPANY:

Compid	CompName

------ --------
1 IBM 2 FORD 3 TESTCORP

EMPLOYEE:

EmpId	Name	CompId	Active
------	------	------	------
1	Bob	1	1
2	Bill	1	1
3	John	1	0
4	Peter	2	1


I want to make a script, showing the number of active and non-active employees, at the different companies.

The output should look like this:

Compid	Compname	active	non-active

------ -------- ------ ----------
1 IBM 2 1 2 FORD 1 0 3 TESTCORP 0 0

I have made a script, that works.

SELECT 	C.Compid, 
	C.Compname, 
	SELECT Count(1) FROM EMPLOYEE E WHERE E.Active=1 AND
E.Compid=C.Compid),
	SELECT Count(1) FROM EMPLOYEE E WHERE E.Active=0 AND
E.Compid=C.Compid)
FROM COMPANY C Is this the right way to do it, or is there a better way ???

Regards,

Ole Received on Wed Apr 20 2005 - 03:50:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US