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: Ken Denny <ken_at_kendenny.com>
Date: 20 Apr 2005 05:36:07 -0700
Message-ID: <1114000567.879213.97360@z14g2000cwz.googlegroups.com>


I would do one thing differently than Rene. I would calculate the non-active a bit differently. I would use this to calculate non-active:

sum(decode(nvl(e.active,0),0,1,0))

Rene Nyffenegger wrote:
> > 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:36:07 CDT

Original text of this message

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