informix sql syntax - nest count, sum, round


let me apologize lay probity doubt (i listened jeff's podcast pleasantness peculiarity questions "dumbed down"), nonetheless i'm stuck. i'm controlling aquadata strike informix db. there quirky small nuances between ms sql informix sql. anyway, i'm perplexing rudimentary nested countenance hates me.



select 
score,
count(*) students,
count(finished) finished,
count(finished) / count(*)students
-- round((count(finished) / count(*)students),2)
from now_calc
group score
order score


the line rudimentary multiplication countenance advantage commission finished, accurately i want...i only need outcome lifeless 2 places. commented line (--) does work. i've attempted each mutation i presumably cruise of.



*i'm perplexing lines 5 & 6 during same time






i'm sorry, i should have mentioned now_calc temp list domain names indeed "students " "finished". i named since i'm going removed law loyal comparison i wanted domain names double buttress headings. so, i know saying, formed that, i finished work stealing (*) this:



select 
score,
count(students) students,
count(finished) finished,
round((count(finished) / count(students) * 100),2) perc
from now_calc
group score
order score


i'm including whole query - competence some-more clarity anyone else looking during this. training perspective, it's vicious note wholly reason count works 'finished' domain since box matter finished values 1 zero depending research box statement. box matter exist, counting 'finished' furnish accurate same law counting 'students'.



--count confederate members sum count students (for reference)
select
cohort_yr,
count (*) id,
(select count (*) id prog_enr_rec where cohort_yr zero prog = 'undg' cohort_yr >=1998) grand
from prog_enr_rec
where cohort_yr zero
and prog = 'undg'
and cohort_yr >=1998
group cohort_yr
order cohort_yr;

--cohort members years population
select
id,
cohort_yr,
cl,
enr_date,
prog
from prog_enr_rec
where cohort_yr zero
and prog = 'undg'
and cohort_yr >=1998
order cohort_yr
into temp cocktail log;

--which race 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 log;

--which race graduated presumably 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 (select pop.id
pop, ed_rec
where pop.id = ed_rec.id
pop.prog = ed_rec.prog
ed_rec.deg_earn[1,1] = 'b'
ed_rec.sch_id = 10)))
into temp grad log;

--combine those presumably graduated still attending
select * att
union
select * grad
into temp all_fin log;

--act scores students race have measure (inner join discharge zero values)
--score > 50 eliminates have information opening errors - sat scores 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 pop.id
into temp pop_score log;

select
pop.id students,
box all_fin.fin = 'y' following 1 else zero finish finished,
pop_score.score
from pop, pop_score, outdoor all_fin
where pop.id = all_fin.id
and pop.id = pop_score.id
into temp now_calc log;

select
score,
count(students) students,
count(finished) finished,
round((count(finished) / count(students) * 100),2) perc
from now_calc
group score
order score


thanks!



Comments

Popular posts from this blog

list macos calm editors formula editors

how i practical urls indicate .aspx pages asp.net deployed an iis? (preferably but iis)

jaxb - xjc - reworking generated typesafe enum category members