ste.cox - Thursday, June 21, 2018 9:27 AM

Hi,

not sure if this is possible but i am trying to build a query that can gives me a total for a group of items.

the query i have so far is:

select
t3.e_reference "Group",
t1.p_surname "Surname",

case when(
case when SUBSTRING(t15.m_reference,3,2) = 'A2' then
   SUM((case when t6.e_grade = 'A*' then 6
     when t6.e_grade = 'A' then 5
     when t6.e_grade = 'B' then 4
     when t6.e_grade = 'C' then 3
     when t6.e_grade = 'D' then 2
     when t6.e_grade = 'E' then 1
     when t6.e_grade = 'U' then 0 else null end) -
     (case when t5.sc_reference3 = 'A*' then 6
     when t5.sc_reference3 = 'A' then 5
     when t5.sc_reference3 = 'B' then 4
     when t5.sc_reference3 = 'C' then 3
     when t5.sc_reference3 = 'D' then 2
     when t5.sc_reference3 = 'E' then 1
     when t5.sc_reference3 = 'U' then 0 else null end))
when SUBSTRING(t15.m_reference,3,2) = 'ZA' then
   SUM((case when t6.e_grade = 'D*' then 4
     when t6.e_grade = 'D' then 3
     when t6.e_grade = 'M' then 2
     when t6.e_grade = 'P' then 1
     when t6.e_grade = 'U' then 0 else null end) -
     (case when t5.sc_text1 = 'D*' then 4
     when t5.sc_text1 = 'D' then 3
     when t5.sc_text1 = 'M' then 2
     when t5.sc_text1 = 'P' then 1
     when t5.sc_text1 = 'U' then 0 else null end))
when SUBSTRING(t15.m_reference,3,2) = 'ZD' then
   SUM((case when t6.e_grade = 'D*D*D*' then 12
     when t6.e_grade = 'D*D*D' then 11
     when t6.e_grade = 'D*DD' then 10
     when t6.e_grade = 'DDD' then 9
     when t6.e_grade = 'DDM' then 8
     when t6.e_grade = 'DMM' then 7
     when t6.e_grade = 'MMM' then 6
     when t6.e_grade = 'MMP' then 5
     when t6.e_grade = 'MPP' then 4
     when t6.e_grade = 'PPP' then 3
     when t6.e_grade = 'U' then 0 else null end) -
     (case when t5.sc_text3 = 'D*D*D*' then 12
     when t5.sc_text3 = 'D*D*D' then 11
     when t5.sc_text3 = 'D*DD' then 10
     when t5.sc_text3 = 'DDD' then 9
     when t5.sc_text3 = 'DDM' then 8
     when t5.sc_text3 = 'DMM' then 7
     when t5.sc_text3 = 'MMM' then 6
     when t5.sc_text3 = 'MMP' then 5
     when t5.sc_text3 = 'MPP' then 4
     when t5.sc_text3 = 'PPP' then 3
     when t5.sc_text3 = 'U' then 0 else null end)) 
else null end) = -2 then 1 else 0 end as '-2'

from
capd_person t1 with (nolock)
inner join capd_student t2 with (nolock) on t2.s_id=t1.p_id
inner join capd_moduleenrolment t6 with (nolock) on t1.p_id=t6.e_student
inner join capd_examsubjectentry t8 with (nolock) on t8.ese_id=t6.e_id
inner join capd_moduleenrolment t3 with (nolock) on t2.s_studenttutorgroup=t3.e_id
inner join capd_studentcustom t5 with (nolock) on t1.p_id=t5.sc_customstudent
inner join capd_studentcustom t7 with (nolock) on t1.p_id=t7.sc_customstudent
inner join capd_module t9 with (nolock) on t6.e_module=t9.m_id
inner join capd_examsubject t10 with (nolock) on t10.es_id=t9.m_id
inner join capd_examsubboardspecific t12 with (nolock) on t12.esbs_id=t9.m_id
left outer join caps_valid_codes t13 with (nolock) on t13.vc_code=t12.esbs_gceoptionitem and t13.vc_domain='gceoptionitem'
left outer join capd_moduleenrolment t14 with (nolock) on t6.e_parent=t14.e_id
inner join capd_module t11 with (nolock) on t9.m_id=t11.m_id
left outer join capd_examsubject t16 with (nolock) on t16.es_id=t11.m_id
left outer join capd_module t15 with (nolock) on t14.e_module=t15.m_id

where
(t8.ese_examsubjectentry=-1)
and ((t6.e_points is not null) or (t6.e_grade is not null))
and (t10.es_examsubjectreference!='CREF')
and (t13.vc_name='Cert')
and (t6.e_grade is not null)
and (t15.m_level in ('A2','Z','A')) -- change to level A for 2018 onwards
and (t6.e_name not like '%%AEA%%')
and (t7.sc_type='PA')
and (t5.sc_type='AL')
and (getdate()>isnull(t16.es_examsubexpawarddate,'01-Sep-2000')+' 06:00')
and (t10.es_examsubjectyear in ('2017','2016','2015')) -- change year
and t3.e_reference like '_ _-%%' -- change date
and (t15.m_end<='01-aug-'+dbo.tochar(Datepart(yyyy,dbo.addmonths(getdate(),-8)),null))
and (t3.e_end>='01-aug-'+dbo.tochar(Datepart(yyyy,dbo.addmonths(getdate(),-20)),null))
and (t3.e_start<='01-aug-'+dbo.tochar(Datepart(yyyy,dbo.addmonths(getdate(),-8)),null))
and t15.m_reference not like '__z3-%%'
and t1.p_id = 100001146178659

group by
t1.p_surname,
t3.e_reference,
t15.m_reference

which gives me:

        

Group	Surname	-2
A 1-16	Nguyen	1
A 1-16	Nguyen	1

and i want to be able to sum these grouped by group so i would get

Group	Surname	-2
A 1-16	Nguyen	2

but i cant get another layer of grouping in there as i get the following error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

is there any way around this? am i missing something obvious?