Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!rutgers!gatech!purdue!bu-cs!dartvax!eleazar.dartmouth.edu!carl
From: carl@eleazar.dartmouth.edu (Carl Pedersen)
Newsgroups: comp.databases
Subject: Re: SQL select,group by, nested???
Summary: Join a View of the total.
Keywords: SQL, view, join, cartesian
Message-ID: <14615@dartvax.Dartmouth.EDU>
Date: 24 Jul 89 15:17:32 GMT
References: <1910001@hpmcaa.mcm.hp.com>
Sender: news@dartvax.Dartmouth.EDU
Reply-To: carl.pedersen@dartmouth.edu (Carl Pedersen)
Organization: Dartmouth College, Hanover, NH
Lines: 47

In article <1910001@hpmcaa.mcm.hp.com> nacer@hpmcaa.mcm.hp.com
(Abdenacer Moussaoui) writes:
>Consider the following select stmt which lists the distribution of
>teachers per department and gives the corresponding percentages
>
>select 
>	deptname, 
>	teacher, 
>	count(*) no.of.teachers, 
>	count(*) / 125 * 100 percentage
>from dept
>group by deptname, teacher;
>
>125 is the current total. How can I make this select compute the 125
>(ie. select count(*) from dept) so that it isn't hard-coded like in
>the above example.  I use Informix.
>
>Thank you!
>-- nacer@mist.orst.cs.edu 

The way I've done stuff like this is to create a view that
computes the total, e.g., in your case:

  create view dept_total as select count(*) count from dept;

Then, you can do your select as:

 select deptname,
        teacher,
        count(*) "no.of.teachers",
        count(*) / dept_total.count * 100 percentage
    from dept, dept_total
    group by deptname, teacher, dept_total.count;

One might expect this to perform poorly, but ORACLE seems to
handle it acceptably, and maybe INFORMIX will, too.  I'd be
interested to hear of people's experience with this, or any
other ways to solve this problem.

Note that this is a rare example of a case where one wants the
entire cartesian product of two tables, with no restricting
WHERE clauses.  This is mainly because one of the tables
(actually a view) has only one row.

I consider it a drawback of SQL that one is not allowed to embed
sub-queries in the FROM clause of a select statement.  It seems
like an obvious extension, but perhaps I've missed something.