Divide one select result by another select result

Question

I've looked at the other similar questions, and they seem to be a little bit different than what I'm trying to do.

I have a single query, where I'm selecting two count values, and I want to divide one by the other.

Here is my working code:

SELECT (SELECT count(DISTINCT s.lastfirst) 
          FROM students s
          JOIN cc ON s.id = cc.studentid
          JOIN courses c on cc.course_number = c.course_number
         WHERE cc.schoolid='109'
           AND c.course_name LIKE 'AP %' 
           AND substr(cc.termid,0,1) <> '-'
           AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') AND  
               to_date('08/01/2011','MM/DD/YYYY')) as AP, 
       (SELECT count(DISTINCT s.lastfirst) 
          FROM students s
          JOIN cc ON s.id = cc.studentid
          JOIN courses c on cc.course_number = c.course_number
         WHERE cc.schoolid = '109'
           AND substr(cc.termid,0,1) <> '-'
           AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') 
           AND to_date('08/01/2011','MM/DD/YYYY')) as ttl
 FROM DUAL

Here is where I break it:

SELECT (SELECT count(DISTINCT s.lastfirst) 
          FROM students s
          JOIN cc ON s.id = cc.studentid
          JOIN courses c on cc.course_number = c.course_number
         WHERE cc.schoolid='109'
           AND c.course_name LIKE 'AP %' 
           AND substr(cc.termid,0,1) <> '-'
           AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') 
           AND to_date('08/01/2011','MM/DD/YYYY')) as AP, 
       (SELECT count(DISTINCT s.lastfirst) 
          FROM students s
          JOIN cc ON s.id = cc.studentid
          JOIN courses c on cc.course_number = c.course_number
         WHERE cc.schoolid = '109'
           AND substr(cc.termid,0,1) <> '-'
           AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
           AND to_date('08/01/2011','MM/DD/YYYY')) as ttl,
      (AP / ttl) as pcnt
 FROM DUAL

Could somebody please tell me the proper way to do this?

Thank You

Solution

The simplest answer is just divide the two values:

SELECT (SELECT count(DISTINCT s.lastfirst) 

FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number    
WHERE cc.schoolid='109'    
AND c.course_name LIKE 'AP %'     
AND substr(cc.termid,0,1) <> '-'    
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') 
    AND to_date('08/01/2011','MM/DD/YYYY')) /    
(SELECT count(DISTINCT s.lastfirst)     
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number       
WHERE cc.schoolid = '109'    
AND substr(cc.termid,0,1) <> '-'    
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') 
    AND to_date('08/01/2011','MM/DD/YYYY')) as quotient

FROM DUAL

---

If you want to keep the values and get the quotient, you could do:

SELECT AP, ttl, AP/ttl AS quotient
FROM (
SELECT (SELECT count(DISTINCT s.lastfirst) 
          FROM students s
          JOIN cc ON s.id = cc.studentid
          JOIN courses c on cc.course_number = c.course_number
         WHERE cc.schoolid='109'
           AND c.course_name LIKE 'AP %' 
           AND substr(cc.termid,0,1) <> '-'
           AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') AND  
               to_date('08/01/2011','MM/DD/YYYY')) as AP, 
       (SELECT count(DISTINCT s.lastfirst) 
          FROM students s
          JOIN cc ON s.id = cc.studentid
          JOIN courses c on cc.course_number = c.course_number
         WHERE cc.schoolid = '109'
           AND substr(cc.termid,0,1) <> '-'
           AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') 
           AND to_date('08/01/2011','MM/DD/YYYY')) as ttl
 FROM DUAL)

but at this point you may as well just do the division in the calling code rather than the SQL, since you're just selecting one result row with two scalar values anyway.

Comments

[Brian Brock]

Thank you very much! This is exactly the outcome I was looking for! I'll tuck this into my bag of tricks! :-)

[lc.]

@BrianBrock It basically takes your query as a subquery and selects from it. There's probably a slightly more optimized way to write your query in the first place, but I'd only worry about that if things start noticeably slowing down.