title: Informix SQL Syntax - Nest Count, Sum, Round
body: <p>Let me apologize in advance for the simplicity of this question (I heard Jeff's podcast and his concern that the quality of the questions will be "dumbed down"), but I'm stuck.  I'm using AquaData to hit my Informix DB.  There are quirky little nuances between MS SQL and Informix SQL.  Anyway, I'm trying to do a simple nested expression and it hates me.</p>
  
  <pre><code>select 
    score,
    count(*) students,
    count(finished) finished,
    count(finished) / count(*)students   
  --  round((count(finished) / count(*)students),2) 
  from now_calc 
  group by score
  order by score
  </code></pre>
  
  <p>The line with the simple division expression returns the percentage of people who finished, which exactly what I want...I just need the result rounded to 2 places.  The commented line (--) does not work.  I've tried every variation I can possibly think of.  </p>
  
  <p>*I'm NOT trying to use lines 5 &amp; 6 at the same time</p>
  
  <hr>
  
  <p>I'm sorry, I should have mentioned that now_calc is a temp table and that the field names actually are "students " and "finished".  I had them named like that because I'm going to spit these results straight into Excel and I wanted the field names to double as column headings.  So, I understand what you are saying, and based on that, I made it work by removing the (*) like this:</p>
  
  <pre><code>select 
    score,
    count(students) students,
    count(finished) finished,
    round((count(finished) / count(students) * 100),2) perc
  from now_calc 
  group by score
  order by score
  </code></pre>
  
  <p>I'm including the entire query - it might make more sense to anyone else looking at this.  From a learning perspective, it's important to note the only reason count works on the 'finished' field is because of the Case statement that made the values 1 or null depending on the evaluation of the Case statement. If that case statement did not exist, counting 'finished' would produce the exact same results as counting 'students'.</p>
  
  <pre><code>--count of cohort members and total count of all students (for reference)
  select 
    cohort_yr, 
    count (*) id,
    (select count (*) id from prog_enr_rec where cohort_yr is not null and prog = 'UNDG' and cohort_yr &gt;=1998) grand
  from prog_enr_rec
  where cohort_yr is not null 
  and prog = 'UNDG'
  and cohort_yr &gt;=1998
  group by cohort_yr
  order by cohort_yr;
  
  --cohort members from all years for population
  select 
    id,  
    cohort_yr,
    cl,
    enr_date,
    prog
  from prog_enr_rec
  where cohort_yr is not null 
  and prog = 'UNDG'
  and cohort_yr &gt;=1998
  order by cohort_yr
  into temp pop with no log;
  
  --which in population are still attending (726)
  select 
    pop.id, 
    'Y' fin 
  from pop, stu_acad_rec
  where pop.id = stu_acad_rec.id 
  and pop.prog = stu_acad_rec.prog
  and sess = 'FA'
  and yr = 2008
  and reg_hrs &gt; 0
  and stu_acad_rec.cl[1,1] &lt;&gt; 'P'
  into temp att with no log;
  
  --which in population graduated with either A or B deg (702)
  select 
    pop.id,
    'Y' fin
  from pop, ed_rec
  where pop.id = ed_rec.id
  and pop.prog = ed_rec.prog
  and ed_rec.sch_id = 10 
  and (ed_rec.deg_earn[1,1] = 'B'
  or  (ed_rec.deg_earn[1,1] = 'A'
  and pop.id not in (select pop.id 
             from pop, ed_rec
             where pop.id = ed_rec.id
             and pop.prog = ed_rec.prog
             and ed_rec.deg_earn[1,1] = 'B'
             and ed_rec.sch_id = 10)))
  into temp grad with no log;
  
  --combine all those that either graduated or are still attending
  select * from att
  union
  select * from grad
  into temp all_fin with no log;
  
  --ACT scores for all students in population who have a score (inner join to eliminate null values)
  --score &gt; 50 eliminates people who have data entry errors - SAT scores in ACT field
  --2270
  select 
    pop.id,
    max (exam_rec.score5) score
  from pop, exam_rec
  where pop.id = exam_rec.id
  and ctgry = 'ACT'
  and score5 &gt; 0 
  and score5 &lt; 50
  group by pop.id
  into temp pop_score with no log;
  
  select 
    pop.id students,
    Case when all_fin.fin = 'Y' then 1 else null end finished,
    pop_score.score
  from pop, pop_score, outer all_fin
  where pop.id = all_fin.id 
  and pop.id = pop_score.id
  into temp now_calc with no log;
  
  select 
    score,
    count(students) students,
    count(finished) finished,
    round((count(finished) / count(students) * 100),2) perc
  from now_calc 
  group by score
  order by score
  </code></pre>
  
  <p>Thanks!</p>