Getting sum of an alias

Question

Trying to take the sum of the alias studentsnotreviewed, what is the best way to approach this I've been stuck trying different things. I want to get the total number for the alias of studentsnotreviewed.

select x.* from
(
select s.id as School,
e.id as examID, e.exam_start as examstart, e.exam_end as examend, 
(select count(*) from students st where e.id=st.exam_id and (st.review_flag='' or st.review_flag is null)) as studentsnotreviewed,
e.exam_end + Interval 2 day as 'DueDate',
(select SUM(studentsnotreviewed)) as SUM
from exams e
#left join exams on st.exam_id = e.id
left join schools s on e.school_id=s.id

where e.exam_start < now() and e.exam_start>'2021-01-01' and e.practice=0) as x
where studentsnotreviewed>0 and (studentsnotreviewed>15 or examend < now())
order by duedate asc, studentsnotreviewed desc

[Sample data]

School	examID	examstart	examend	studentsnotreviewed	duedate	sum
343	458092	Mar 16, 2021, 3:52 PM	Aug 28, 2017, 12:59 AM	2	Aug 30, 2017, 12:59 AM	2
125	360216	Jan 7, 2021, 11:55 AM	Jul 26, 2018, 11:17 PM	27	Jul 28, 2018, 11:17 PM	27

[Desired result] The sum of the alias for studentsnotreviewed.

Total studentsnotreviewed
29

Answer 1

I believe metabase supports WITH, so you can alias your whole query as a temporary block of data, and then reuse it as if it were a table:

with x as
(
  select 
    s.id as School,
    e.id as examID, 
    e.exam_start as examstart, 
    e.exam_end as examend, 
    (select count(*) from students st where e.id=st.exam_id and (st.review_flag='' or st.review_flag is null)) as studentsnotreviewed,
    e.exam_end + Interval 2 day as 'DueDate',
  from
    exams e
    left join exams on st.exam_id = e.id
    left join schools s on e.school_id=s.id
  where e.exam_start < now() and e.exam_start>'2021-01-01' and e.practice=0
) 

select
  x.*,
  (select SUM(studentsnotreviewed) from x) as SUM
from
  x
where 
  studentsnotreviewed>0 and 
  (studentsnotreviewed>15 or examend < now())
order by 
  duedate asc, studentsnotreviewed desc

Your query has a slight syntax error it seems - you mention # and don't mention students st in the outer query.. I'm sure you can fix this up

I assumed you wanted the 29 as a value repeating in the resultset. If you literally just want a single value of 29, I suppose it could be given by:

select count(*) 
from 
  exams e 
  inner join students st on e.id=st.exam_id 
where
  (st.review_flag='' or st.review_flag is null) and
  e.exam_start < now() and e.exam_start>'2021-01-01' and 
  e.practice=0