SQL subtracting 2 subqueries

Fairly new to SQL and I wanted to see if I could get some help with subtraction. I want to subtract studentstaken – studentsnotreviewed and then as assign the alias total to the operation. I was thinking something along the lines of

select ((select count(*) from students where exams.id=students.exam_id) - (select count(*) from students where exams.id=students.exam_id and (students.review_flag='' or students.review_flag is null)) ) as 'Total'

But getting some syntax problems I’m not too sure about.

select x.* from
(

select schools.name as School,  
exams.name as name, exams.exam_start as examstart, exams.exam_end as examend, 
(select count(*) from students where exams.id=students.exam_id) as studentstaken,
(select count(*) from students where exams.id=students.exam_id and (students.review_flag='' or students.review_flag is null)) as studentsnotreviewed,
#select () as 'Total'
case when exam_end< (now() - interval 2 day) then 'Yes' else 'No' end as 'Overdue',
exam_end + Interval 2 day as 'DueDate',
(select value from exam_options where exams.id=exam_options.exam_id and option_id=5) as IDtoggle,
(select value from exam_options where exams.id=exam_options.exam_id and option_id=6) as Roomscantoggle
from exams
left join taggables on exams.school_id=taggable_id
left join schools on exams.school_id=schools.id
where tag_id=12 and exam_start < now() and exam_start>'2021-01-01' and practice=0) as x
where studentsnotreviewed>0 and (studentsnotreviewed>15 or examend < now())  and (IDtoggle=1 or Roomscantoggle=1)
order by duedate asc, studentsnotreviewed desc

[Sample data]

School	Name	examstart	examend	studentstaken	studentsnotreviewed	IDtoggle	Roomscanetoggel
University of Texas Health Sciences Houston	EXAM 1	Feb 3, 2021, 10:55 AM	Feb 3, 2021, 1:30 PM	26	2	1	1
University of Texas Health Sciences Houston	EXAM 1	Feb 4, 2021, 10:45 AM	Feb 4, 2021, 12:59 PM, 12:59 AM	31	1	1	1

[Desired result]

School	Name	examstart	examend	studentstaken	studentsnotreviewed	total	IDtoggle	Roomscanetoggel
University of Texas Health Sciences Houston	EXAM 1	Feb 3, 2021, 10:55 AM	Feb 3, 2021, 1:30 PM	26	2	24	1	1
University of Texas Health Sciences Houston	EXAM 1	Feb 4, 2021, 10:45 AM	Feb 4, 2021, 12:59 PM, 12:59 AM	31	1	30	1	1

Answer

It should be ok.

select x.* 
from
(
    select schools.name as School,  
        exams.name as name, exams.exam_start as examstart, exams.exam_end as examend, 
        (select count(*) from students where exams.id=students.exam_id) as studentstaken,
        (select count(*) from students where exams.id=students.exam_id and (students.review_flag='' or students.review_flag is null)) as studentsnotreviewed,
        (select count(*) from students where exams.id=students.exam_id) 
        - (select count(*) from students where exams.id=students.exam_id and (students.review_flag='' or students.review_flag is null)) as 'Total',
   ... rest of the query

Advertisement

Answer