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 -> Re: SQL-statement

Re: SQL-statement

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Wed, 20 Apr 2005 12:20:07 +0000 (UTC)
Message-ID: <d45hdn$t2q$1@klatschtante.init7.net>


> 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
  company c left join employee e on
  c.id = e.compid
group by
  c.id,
  c.name
order by
  c.id;

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

Original text of this message

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