nacer@hpmcaa.mcm.hp.com (Abdenacer Moussaoui) (07/20/89)
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
carl@eleazar.dartmouth.edu (Carl Pedersen) (07/24/89)
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.
nacer@hpmcaa.mcm.hp.com (Abdenacer Moussaoui) (07/27/89)
>/ hpmcaa:comp.databases / carl@eleazar.dartmouth.edu (Carl Pedersen) / 8:17 am Jul 24, 1989 / >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. >---------- > I agree this fairly complicated. I whish if the SQL environment keept track of its database statistics consistently then, it should be fairly "easy" to provide some function call say rowcount() such as dBase reccount(). If embedding was allowed I whish it would not have to execute the nested query (select count(*) from dept) every time, since it always leads the same result (ie. the size of table dept). -- nacer @ mist.orst.cs.edu