Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL-statement
> 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 ???
The 'right' way is in the eye of the beholder... Or as perlianer say, there's more than one way to do it. Anyway, I'd write your query like so:
create table company (
id number primary key,
name varchar2(10)
);
create table employee (
id number primary key,
compid references company,
name varchar2(10),
active number(1) check (active in (0, 1))
);
insert into company values (1, 'IBM'); insert into company values (2, 'Ford'); insert into company values (3, 'Test Comp'); insert into employee values (1, 1, 'Bob' , 1); insert into employee values (2, 1, 'Bill' , 1);insert into employee values (3, 1, 'John' , 0); insert into employee values (4, 2, 'Peter', 1);
select
c.id comp_id, c.name comp_name, sum(nvl(e.active,0)) active,count(e.active) - sum(nvl(e.active,0)) non_active from
But I wouldn't say that my solution is more right than yours, but probably a bit more efficient.
hth
Rene
-- Rene Nyffenegger http://www.adp-gmbh.ch/Received on Wed Apr 20 2005 - 07:20:07 CDT