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