Informix SQL Syntax - Nest Count, Sum, Round

Question

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.

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

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.

*I'm NOT trying to use lines 5 & 6 at the same time

Answer 1

SELECT
        score,
        count(*) students,
        count(finished) finished,
        count(finished) / count(*) AS something_other_than_students,   
        round((count(finished) / count(*)),2) AS rounded_value
    FROM now_calc 
    GROUP BY score
    ORDER BY score;

Note that the output column name 'students' was being repeated and was also confusing you. The AS I used is optional.

I've now formally validated the syntax against IDS, and it is usable:

Black JL: sqlcmd -Ffixsep -d stores -xf xx.sql | sed 's/        //g'
+ create temp table now_calc(finished CHAR(1), score INTEGER, name CHAR(10) PRIMARY KEY);
+ insert into now_calc values(null, 23, 'a');
+ insert into now_calc values('y',  23, 'b');
+ insert into now_calc values('y',  23, 'h');
+ insert into now_calc values('y',  23, 'i');
+ insert into now_calc values('y',  23, 'j');
+ insert into now_calc values('y',  43, 'c');
+ insert into now_calc values(null, 23, 'd');
+ insert into now_calc values('y',  43, 'e');
+ insert into now_calc values(null, 23, 'f');
+ insert into now_calc values(null, 43, 'g');
+ SELECT
        score,
        count(*) students,
        count(finished) finished,
        count(finished) / count(*) AS something_other_than_students,
        round((count(finished) / count(*)),2) AS rounded_value
    FROM now_calc
    GROUP BY score
    ORDER BY score;
 23|       7|       4| 5.71428571428571E-01|      0.57
 43|       3|       2| 6.66666666666667E-01|      0.67
Black JL:

I let 'finished' take nulls because the only reason for 'count(finished) / count(*)' not to return 1 is if 'finished' accepts nulls -- not very good table design, though. And I put 7 rows with score 23 to get a large number of decimal places (and then changed one row with score 43 to generate a second number with a large number of decimal places).

Answer 2

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:

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

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'.

--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 >=1998) grand
from prog_enr_rec
where cohort_yr is not null 
and prog = 'UNDG'
and cohort_yr >=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 >=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 > 0
and stu_acad_rec.cl[1,1] <> '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 > 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 > 0 
and score5 < 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

Thanks!