Summation on distinct query results (Aggregate on result)

SQL++
1

Hi,

I’m looking for a way to return the total time duration & counts on some documents. These are some exam & grading documents. I will need to find out the total exams that has been marked & the total amount of time these exams has spent. There can be multiple graders to make the same exam, it will generate a new grading document. Once an exam has been marked, “completed” will become true.

The issue I’m having is that I can’t seem to get the right time duration. Same examID will get returned multiple times if there are more than one grader has marked the exam. I’m not sure how to apply DISTINCT to individual startTime or endTime. Is there a way I do summation after I’ve get all the distinct documents?

Please see my query and documents example below. Thanks in advance.

Here is the query I’ve tried, I was able to get the correct count but not the time duration.

SELECT SUM(( MILLIS(b.endTime) - MILLIS(b.startTime))/1000/3600) as duration, 
COUNT(meta(b).id)  as count
FROM mybucket a JOIN mybucket b  ON KEYS  a.examId
WHERE a.type == 'gradingDocument' AND a.completed == TRUE and b.examType='Math'

#Exam Document

{
	examtID: "exam123",
	startTime: "2018/5/25 10:00:00 AM",
	endTime: "2018/5/25 10:50:00 AM",
	examType: "math"
}

#Grading Document

{
	gradingID: "grading123",
	grader: "grader123",
	completed: true, 
	examId: "exam123"
}

{
	gradingID : "grading485",
	grader: "grader999",
	completed: true, 
	examId: "exam123"
}
2 
SELECT SUM(( MILLIS(b.endTime) - MILLIS(b.startTime))/1000/3600) as duration,
       COUNT(meta(b).id)  as count
FROM (SELECT RAW a.examId
      FROM mybucket AS a
      WHERE a.type == 'gradingDocument' AND a.completed == TRUE
      GROUP BY a.examId ) AS a
JOIN mybucket b ON KEYS  a
WHERE b.examType='Math';
 
  OR

SELECT SUM(( MILLIS(b.endTime) - MILLIS(b.startTime))/1000/3600) as duration,
               COUNT(meta(b).id)  as count
FROM (SELECT DISTINCT a.examId
              FROM mybucket AS a
              WHERE a.type == 'gradingDocument' AND a.completed == TRUE ) AS a
JOIN mybucket b ON KEYS  a.examId
WHERE b.examType='Math';
3

thanks for the reply. I’ve always wonder how to query/select from another query result. this resolved the issue.