SUM(CASE(CASE(SUM()))) ?

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  June 21, 2018 at 9:27 am

  #406756

  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?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  June 21, 2018 at 9:33 am

  #1994853

  I realise this isn't an answer to your question, however, why are you using the nolock query hint on every table? Also, please learn how to Alias objects in a helpful manner: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  June 21, 2018 at 10:16 am

  #1994861

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

  am i missing something obvious?

  Yes, you're missing the fact that addition is associative.  The SUM should be the outermost operator instead of the innermost.

  Also, your CASE expression seems overly complex.  I've never needed to have nested CASE expressions.  Since you haven't provided sample data, I can't begin to tell you how to rewrite it it simplify the expression.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  June 21, 2018 at 11:35 am

  #1994886

  Why would you have a dbo.addmonths function when you have a native DATEADD function?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 21, 2018 at 1:50 pm

  #1994911

  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?

  The simple solution is to place this query in a CTE (aka Common Table Expression), and then SELECT your columns FROM the CTE and use your outer GROUP BY in that query.  Again, with such complex CASE statements, there may be ways to considerably simplify this.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Thom A

  SSC Guru

  Points: 99126

  More actions

  June 22, 2018 at 2:06 am

  #1994940

  I had to do something about some parts of that query, it was driving my "OCD" up the fall. The aliasing is one of my biggest pet peeves, and when i realised that t6 was the 3rd table in the FROM, t8 the 4th and t3 the 5th... Well I had to do something,... There probably could be better Aliases, as the Op uses the same table more than once. Also, I've changed the addmonths function that they had, and left some comments. i've also removed every WITH (NOLOCK), in the absense of a good reason from the OP to why they're using them.

  White space removal courtesy of SSC's awful text editor... Sorry.
  SELECT meg.e_reference AS [GROUP],
   cp.p_surname AS Surname,
   CASE WHEN (CASE WHEN SUBSTRING(md.m_reference, 3, 2) = 'A2' THEN
          SUM((CASE WHEN me.e_grade = 'A*' THEN
               6
             WHEN me.e_grade = 'A' THEN
               5
             WHEN me.e_grade = 'B' THEN
               4
             WHEN me.e_grade = 'C' THEN
               3
             WHEN me.e_grade = 'D' THEN
               2
             WHEN me.e_grade = 'E' THEN
               1
             WHEN me.e_grade = 'U' THEN
               0
             ELSE NULL
             END) - (CASE WHEN sc1.sc_reference3 = 'A*' THEN
                   6
                 WHEN sc1.sc_reference3 = 'A' THEN
                   5
                 WHEN sc1.sc_reference3 = 'B' THEN
                   4
                 WHEN sc1.sc_reference3 = 'C' THEN
                   3
                 WHEN sc1.sc_reference3 = 'D' THEN
                   2
                 WHEN sc1.sc_reference3 = 'E' THEN
                   1
                 WHEN sc1.sc_reference3 = 'U' THEN
                   0
                 ELSE NULL
                 END))
        WHEN SUBSTRING(md.m_reference, 3, 2) = 'ZA' THEN
          SUM((CASE WHEN me.e_grade = 'D*' THEN
               4
             WHEN me.e_grade = 'D' THEN
               3
             WHEN me.e_grade = 'M' THEN
               2
             WHEN me.e_grade = 'P' THEN
               1
             WHEN me.e_grade = 'U' THEN
               0
             ELSE NULL
             END) - (CASE WHEN sc1.sc_texcp = 'D*' THEN
                   4
                 WHEN sc1.sc_texcp = 'D' THEN
                   3
                 WHEN sc1.sc_texcp = 'M' THEN
                   2
                 WHEN sc1.sc_texcp = 'P' THEN
                   1
                 WHEN sc1.sc_texcp = 'U' THEN
                   0
                 ELSE NULL
                 END))
        WHEN SUBSTRING(md.m_reference, 3, 2) = 'ZD' THEN
          SUM((CASE WHEN me.e_grade = 'D*D*D*' THEN
               12
             WHEN me.e_grade = 'D*D*D' THEN
               11
             WHEN me.e_grade = 'D*DD' THEN
               10
             WHEN me.e_grade = 'DDD' THEN
               9
             WHEN me.e_grade = 'DDM' THEN
               8
             WHEN me.e_grade = 'DMM' THEN
               7
             WHEN me.e_grade = 'MMM' THEN
               6
             WHEN me.e_grade = 'MMP' THEN
               5
             WHEN me.e_grade = 'MPP' THEN
               4
             WHEN me.e_grade = 'PPP' THEN
               3
             WHEN me.e_grade = 'U' THEN
               0
             ELSE NULL
             END) - (CASE WHEN sc1.sc_text3 = 'D*D*D*' THEN
                   12
                 WHEN sc1.sc_text3 = 'D*D*D' THEN
                   11
                 WHEN sc1.sc_text3 = 'D*DD' THEN
                   10
                 WHEN sc1.sc_text3 = 'DDD' THEN
                   9
                 WHEN sc1.sc_text3 = 'DDM' THEN
                   8
                 WHEN sc1.sc_text3 = 'DMM' THEN
                   7
                 WHEN sc1.sc_text3 = 'MMM' THEN
                   6
                 WHEN sc1.sc_text3 = 'MMP' THEN
                   5
                 WHEN sc1.sc_text3 = 'MPP' THEN
                   4
                 WHEN sc1.sc_text3 = 'PPP' THEN
                   3
                 WHEN sc1.sc_text3 = 'U' THEN
                   0
                 ELSE NULL
                 END))
        ELSE NULL
        END) = -2 THEN
      1
    ELSE 0
    END AS [-2]
FROM capd_person cp
  INNER JOIN capd_student cs ON cs.s_id = cp.p_id
  INNER JOIN capd_moduleenrolment me ON cp.p_id = me.e_student
  INNER JOIN capd_examsubjectentry ese ON ese.ese_id = me.e_id
  INNER JOIN capd_moduleenrolment meg ON cs.s_studenttutorgroup = meg.e_id
  INNER JOIN capd_studentcustom sc1 ON cp.p_id = sc1.sc_customstudent
  INNER JOIN capd_studentcustom sc2 ON cp.p_id = sc2.sc_customstudent
  INNER JOIN capd_module m ON me.e_module = m.m_id
  INNER JOIN capd_examsubject es ON es.es_id = m.m_id
  INNER JOIN capd_examsubboardspecific ess ON ess.esbs_id = m.m_id
  LEFT OUTER JOIN caps_valid_codes vc ON vs.vc_code = ess.esbs_gceoptionitem
              AND vs.vc_domain = 'gceoptionitem'
  LEFT OUTER JOIN capd_moduleenrolment mep ON me.e_parent = mep.e_id
  INNER JOIN capd_module mm ON m.m_id = mm.m_id
  LEFT OUTER JOIN capd_examsubject es ON es.es_id = mm.m_id
  LEFT OUTER JOIN capd_module md ON mep.e_module = md.m_id
WHERE (ese.ese_examsubjectentry = -1)
AND ((me.e_points IS NOT NULL)
  OR (me.e_grade IS NOT NULL))
AND (es.es_examsubjectreference != 'CREF')
AND (vs.vc_name = 'Cert')
AND (me.e_grade IS NOT NULL)
AND (md.m_level IN ('A2', 'Z', 'A')) -- change to level A for 2018 onwards
AND (me.e_name NOT LIKE '%%AEA%%') --This isn't SARGable, so it's going to be bad for performance
AND (sc2.sc_type = 'PA')
AND (sc1.sc_type = 'AL')
AND (GETDATE() > ISNULL(es.es_examsubexpawarddate, '01-Sep-2000') + ' 06:00')
AND (es.es_examsubjectyear IN ('2017', '2016', '2015')) -- change year
AND meg.e_reference LIKE '_ _-%%' -- change date --This isn't SARGable, so it's going to be bad for performance
                 --If the _ are mean be be lieral underscores, use [_]
AND md.m_end <= DATEADD(MONTH, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH, 8, GETDATE()), 0))) --I've guessed what really need to happen here. No need for a customer addmonths function.
AND meg.e_end >= DATEADD(MONTH, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH, -20, GETDATE()), 0))) --I've guessed what really need to happen here. No need for a customer addmonths function.
AND meg.e_start <= DATEADD(MONTH, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH, -8, GETDATE()), 0))) --I've guessed what really need to happen here. No need for a customer addmonths function.
AND md.m_reference NOT LIKE '__z3-%%'
AND cp.p_id = 100001146178659;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  June 22, 2018 at 2:18 am

  #1994941

  Sorry about the alias' and the no lock stuff, its not my code, i was passed it from someone who uses a drag and drop programme to build the query so was working with what they gave me.

  thanks for all the help

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  June 22, 2018 at 4:30 am

  #1994951

  sgmunson - Thursday, June 21, 2018 1:50 PM

  The simple solution is to place this query in a CTE (aka Common Table Expression), and then SELECT your columns FROM the CTE and use your outer GROUP BY in that query.  Again, with such complex CASE statements, there may be ways to considerably simplify this.

  Thanks, this has done the job

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 22, 2018 at 12:42 pm

  #1995006

  ste.cox - Friday, June 22, 2018 4:30 AM

  sgmunson - Thursday, June 21, 2018 1:50 PM

  The simple solution is to place this query in a CTE (aka Common Table Expression), and then SELECT your columns FROM the CTE and use your outer GROUP BY in that query.  Again, with such complex CASE statements, there may be ways to considerably simplify this.

  Thanks, this has done the job

  You're very welcome.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)