June 18, 2008 at 4:49 am
I have a table as follows;
Student_Id Date Present_Absent
101 '01/01/2008' P
102 '01/01/2008' P
103 '01/01/2008' P
101 '02/01/2008' P
102 '02/01/2008' A
103 '02/01/2008' P
I wan to find out the student who has been absent for more than 5 times in a year.the query has to fetch the student id and the date.
Can some body help me please.
June 18, 2008 at 5:14 am
If a student is absent for five or more times, which date would you like the query to return?
June 18, 2008 at 5:28 am
What about something like:
SELECT Student_Id,
YEAR(Date),
COUNT(*) AS numberOfAbsences
FROM studentTable
GROUP BY Student_Id, YEAR(Date)
HAVING COUNT(*) > 5
ps: Is this homework?
- Andras
June 18, 2008 at 5:30 am
Andras Belokosztolszki (6/18/2008)
What about something like:
SELECT Student_Id,
YEAR(Date),
COUNT(*) AS numberOfAbsences
FROM studentTable
GROUP BY Student_Id, YEAR(Date)
HAVING COUNT(*) > 5
ps: Is this homework?
- Andras
-- Assumptions: a year is a calendar year 🙂
Andras
June 18, 2008 at 6:32 am
Thanks Andras.It was of great help.
June 18, 2008 at 10:51 am
You need to add a where clause for Present_Absent = 'A' or else you will return any student_ID that has more than 5 entries.
June 19, 2008 at 3:41 am
jim.pennington (6/18/2008)
You need to add a where clause for Present_Absent = 'A' or else you will return any student_ID that has more than 5 entries.
Good point, I should have read the original question more carefully 🙂
SELECT Student_Id,
YEAR(Date),
COUNT(*) AS numberOfAbsences
FROM studentTable
WHERE Present_Absent = 'A'
GROUP BY Student_Id, YEAR(Date)
HAVING COUNT(*) > 5
Regards,
Andras
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply