CODEWITHSUNDEEP
sqloracle-database
Brian Brock
Brian Brock

Reputation: 351

Divide one select result by another select result

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

Upvotes: 5

Views: 16600

Answers (1)

lc.
lc.

Reputation: 116438

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.

Upvotes: 8

Related Questions