SQL SQL to count the number of awards of each student

55057471 · 2016-06-30

How to write an SQL to count the number of awards of each students in a school year? Thanks in advance for your help.

58019970 · 2016-07-04

老師請參考:

https://forum.hkedcity.net/index.php?threads/抽取學生的獎懲數量及操行.145635/

55057471 · 2016-07-04

Sorry. I should have explained in more details. I want to sum the number of awards or scholarships of each student in this school year, not the award point (大過,小過). Thanks in advance.

58019970 · 2016-07-04

請老師參考以下SQL 語句:

Select
schoolyear,
classlevel,
classcode,
classno,
chname,
enname,
sum((CASE WHEN Types= '校內' THEN 1 ELSE 0 END)) '校內獎項',
sum((CASE WHEN Types= '校外' THEN 1 ELSE 0 END)) '校外獎項',
count(Types) '獎項總數'
from
(select
schoolyear,
classlevel,
classcode,
classno,
chname,
enname,
Types,
years,
description
from
(select
b.schyear 'Schoolyear',
b.classlvl 'classlevel',
b.classcode 'classcode',
b.classno 'classno',
b.chname 'chname',
b.enname 'enname',
'校內' 'Types',
cast(dateformat(a.anpdate, 'YYYY') as text) 'years',
((case when trim(a.prefix)<>'' then a.prefix else '' end) + (case when trim(a.descriptionstr)<>'' then a.descriptionstr else '' end) + (case when trim(a.descriptioncode)<>'' then c.ch_des else '' end) + (case when trim(a.position)<>'' then a.position else '' end) + (case when trim(a.naaofferedid)<>'' then e.ch_des else '' end)) 'description'
from tb_stu_anpsturec a
join vw_stu_lateststudent b
on a.suid=b.suid and a.stuid=b.stuid and a.schyear=b.schyear
left outer join tb_naa_naaoffered d
on a.suid=d.suid and a.schyear=d.schyear and a.naaofferedid=d.naaofferedid
left outer join tb_hse_common e
on a.suid=e.suid and d.naacode=e.code_id
and ((e.tb_id='ECACD' and d.naatype='E')
or (e.tb_id='SEVPST' and d.naatype='S')
or (e.tb_id='INTERSCH' and d.naatype='I'))
left outer join tb_hse_common c
on a.suid=c.suid and a.descriptioncode=c.code_id and c.tb_id='PRIZE'
where a.anptype='A'
union all
select
d1.schyear,
d1.classlvl ,
d1.classcode ,
d1.classno ,
d1.chname ,
d1.enname ,
'校外' ,
c1.STARTYR ,
c1.PROG || ' - ' ||c1.PROG_DES
from TB_SLP_STU_PERFME_OUTSCH c1
join vw_stu_lateststudent d1
on c1.suid=d1.suid and c1.stuid=d1.stuid) act
where schoolyear=? and classlevel = ?) a

group by schoolyear, classlevel, classcode, classno, chname, enname, types

order by classcode, classno
Click to expand...

如果老師想一次retrieve 全校學生(不按班級)，可把紅色部分刪除

教城討論區

SQL SQL to count the number of awards of each student

Su.Chow
Expand Collapse

edb-jackal
Expand Collapse

Su.Chow
Expand Collapse

edb-jackal
Expand Collapse

快捷搜尋

SQL SQL to count the number of awards of each student

Su.Chow Expand Collapse

edb-jackal Expand Collapse

Su.Chow Expand Collapse

edb-jackal Expand Collapse

Su.Chow
Expand Collapse

edb-jackal
Expand Collapse

Su.Chow
Expand Collapse

edb-jackal
Expand Collapse