Informix SQL语法 - 嵌套计数,总和,回合

cid 发布于 2018-05-11 count 最后更新 2018-05-11 10:49 159 浏览

让我为这个问题的简单性事先道歉(我听说杰夫的播客和他对问题质量的“沉默”感到担忧),但我被卡住了。我正在使用AquaData来打我的Informix数据库。 MS SQL和Informix SQL之间有些古怪的细微差别。无论如何,我试图做一个简单的嵌套表达式,它恨我。

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
用简单的除法表达式返回完成的人的百分比,这正是我想要的......我只需要将结果舍入到2个位置。注释行( - )不起作用。我试过了我可能想到的每一个变化。 *我不想使用第5& 6在同一时间
我很抱歉,我应该提到now_calc是临时表,并且字段名称实际上是“学生”和“完成”。我让他们这样命名,因为我要将这些结果直接吐到Excel中,并希望字段名称加倍作为列标题。所以,我明白你在说什么,并基于此,我通过删除(*)来实现它的工作:
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
我将整个查询包括在内 - 对于查看此内容的其他人可能更有意义。从学习的角度来看,重要的是要注意计数在“已完成”字段工作的唯一原因是由于Case语句使值为1或null,这取决于对Case语句的评估。如果不存在该案例陈述,则计数“已完成”将产生与计算“学生”完全相同的结果。
--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
谢谢!
已邀请:

gnihil

赞同来自:

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;
请注意,输出列名称“学生”正在重复,并且也令您感到困惑。我使用的AS是可选的。 我现在已经正式验证了针对IDS的语法,并且它是可用的:
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:
我让'完成'采用空值,因为'count(finished)/ count(*)'不返回1的唯一原因是'完成'接受空值 - 不是很好的表设计。而且我把7分的23分数得到大量的小数位(然后用43分改变了一行,产生了第二个有大量小数位的数字)。