const express = require('express')
const queryHelper = require('../../utilities/query')
const router = express.Router()

router.get('/number-of-student', (req, res) => {
  queryHelper.queryAndResponse({
    sql: `select substr(student_id, 1, 2) as academic_year,
	        count(*) as student_count from students 
          group by substr(student_id, 1, 2)`,
    req: req,
    res: res
  })
})

router.get('/average-gpax', (req, res) => {
  queryHelper.queryAndResponse({
    sql: `select substr(student_id, 1, 2) as academic_year, avg(gpax) as avg_gpax
	        from students group by SUBSTRING(student_id, 1, 2);`,
    req: req,
    res: res
  })
})


module.exports = router

@@ -413,16 +413,21 @@
 age-gpax
+-year
 ', (req,
@@ -615,9 +615,8 @@
 , 2)
-;
 %60,%0A 
@@ -650,16 +650,726 @@
  %7D)%0A%7D)%0A%0A
+router.get('/average-gpax-all', (req, res) =%3E %7B%0A  queryHelper.queryAndResponse(%7B%0A    sql: %60select avg(gpax) as avg_gpax from students%60,%0A    req: req,%0A    res: res%0A  %7D)%0A%7D)%0A%0Arouter.get('/leave-student', (req, res) =%3E %7B%0A  queryHelper.queryAndResponse(%7B%0A    sql: %60select count(*) as leave_count from leaves %0A%09        left join students on leaves.sid = students.sid%0A          where semester=2 and year=2016%60,%0A    req: req,%0A    res: res%0A  %7D)%0A%7D)%0A%0Arouter.get('/overtime-student', (req, res) =%3E %7B%0A  queryHelper.queryAndResponse(%7B%0A    sql: %60select count(*) as leave_count from leaves %0A%09        left join students on leaves.sid = students.sid%0A          where semester=2 and year=2016%60,%0A    req: req,%0A    res: res%0A  %7D)%0A%7D)%0A
 %0Amodule.

Add leave, overtime