Re: SQL Question

You could try something like

select count(*), sum(decode(sign(yearsTaught - 8), 1, 1 ,0)) from teacherTable
where subjectTaught = 'Physics';

Where the first value will give you the number of teachers of Physics and the second will give you the number of Physics teachers with more than 8 years.

Not sure if it is faster than what you have. I'd appreciate a reply if you find that there is significant speed difference.

Cheers
Lovan

"James W. Howe" wrote:

> I'm somewhat new to SQL so please excuse me if this is a silly
> question. I have a table in my database which contains thousands of
> records. Each record has one or more fields which hold either a
> boolean value or a specific text string. I need to be able to compute
> the percentage of a subset of records which has a particular value
> set. Currently I do this by making two queries. The first does a
> count of the records retrieved regardless of the setting of the value
> that I care about. The second query adds an additional qualifier to
> test on a field that I'm interested in.
>
> For example, suppose that I had a table of teachers and each record in
> the table had a field indicating the subject taught by the teacher and
> the number of years the teacher had taught that subject. I might want
> to do a query to determine the percentage of Physics teachers who have
> been teaching the subject for more than 8 years. Currently I would
> select all records in the table where the subject field was 'Physics'
> and get a count of the records, something like this:
>
> select count(teacherID) from TeacherTable where
> subjectTaught='Physics';
>
> I would then do another query to get the teachers who have taught more
> than 8 years, something like this:
>
> select count(teacherID) from TeacherTable where
> subjectTaught='Physics' and yearsTaught > 8.
>
> I can then compute the percentage. What I would like to know is if
> there is a simpler way to get this result. One which doesn't involve
> making two separate queries. Is there a way to more efficiently
> compute the result that I'm looking for?
>
> Any help would be appreciated.
Received on Mon Jun 28 1999 - 16:33:23 CEST