SQL to COUNT items associated with individuals

Question

I have a table (ClassEnrolments) which contains student data:

StudentId       Student        Module          Status      EndDate         Credits
12345678        J Bloggs       Introduction    Pass        2014/09/01      10
12345678        J Bloggs       Advanced        Pass        2014/06/01      15
23456789        T Guy          Introduction    Pass        2013/05/25      10
23456789        T Guy          Advanced        Pass        2014/03/21      15

What I want to do is return information on how many modules the student has taken in total during a set period of time, so for example, if we take the above data and look at the period 01/01/2014 to 24/11/2014 it would return the following (based on EndDate)

StudentId        Student        Modules      Credits      AnnivDate
12345678         J Bloggs       2            25           2015/06/01
23456789         T Guy          1            15           2014/05/25

This is using SQL Server 2008 - any help would be greatly appreciated.

I'm now also wondering whether using the same scenario - its possible to count the number of credits obtained from those taken modules?

Another thought! It would be useful to see some sort of anniversary date based on the students foremost module taken. This will help to establish whether the student has obtained the correct amount of credits by the anniversary date. I've toyed with the idea of the DATEADD function, but this seemed to duplicate the student information again.

DATEADD(MONTH, DATEDIFF(MONTH, 0, EndDate()) +12,0) AS AnnivDate

I understand why, as there will be multiple instances, but is there a way to look at the first instance of EndDate?

I would like to ask the purpose of your task, I mean you get the number of modules but not the names of module completed by a student.I am just guessing If you want that only? — Suraj Singh, Nov 24 '14 at 15:41
thats right, yeah. Not necessarily interested in the mod name, just how many were taken in a certain period. — SB1987, Nov 24 '14 at 15:50
thanks everyone for suggestions, all seemed to work well enough! — SB1987, Nov 24 '14 at 15:52
@SB1987 - I didn't get that anniversary date logic how to find students foremost module taken — Pரதீப், Nov 24 '14 at 16:43
Basically lets say a student has 12 months from the time they take the first module from the time period in question (WHERE EndDate BETWEEN '2014-01-01' AND '2014-11-24'). I would need the code to look at the dates of the modules, determine which was studied first, and then add 12 months to it and return this in the AnnivDate column — SB1987, Nov 25 '14 at 08:52

score 1 · Answer 1 · answered Nov 24 '14 at 15:36

1

Try this:

Set dateformat DMY;
Select StudentId, Student, Count(Modules) as ModuleCount
from ClassEnrolments 
Where EndDate >= convert(Datetime, '01-01-2014', 105) and 
EndDate <= convert(Datetime, '24-11-2014', 105)
Group By StudentId, Student

answered Nov 24 '14 at 15:36

Paresh J

2,401
3
24
31

score 0 · Answer 2 · answered Nov 24 '14 at 15:34

0

Try this:

SELECT
   StudentId,
   Student,
   COUNT(*) Modules
FROM ClassEnrolments
WHERE 
   EndDate BETWEEN '2014-01-01' AND '2014-11-24'
GROUP BY 
   StudentId,
   Student

answered Nov 24 '14 at 15:34

LittleSweetSeas

6,786
2
21
26

score 0 · Answer 3 · answered Nov 24 '14 at 15:34

SELECT StudentID, Student, Count(module) AS Modules
FROM ClassEnrolments
GROUP BY StudentID, Student

With date

SELECT StudentID, Student, Count(module) AS Modules
FROM ClassEnrolments
WHERE EndDate BETWEEN '2014-01-01' AND '2014-11-24'
GROUP BY StudentID, Student

Pரதீப் · Accepted Answer · 2014-11-24T16:01:36.833

0

Use Group By and Aggregate Function count to count the Module and filter the data by using Between operator to filter the data between particular date range

SELECT StudentId,
       Student,
       Count(Module) Module,
       Sum(Credits) Credits
FROM   tablename
WHERE  EndDate BETWEEN '2014-01-01' AND '2014-11-24'
GROUP  BY StudentId,
          Student

edited Nov 24 '14 at 16:01

answered Nov 24 '14 at 15:35

Pரதீப்

91,748
19
131
172

this works perfect, does exactly what I wanted - thanks! – SB1987 Nov 24 '14 at 15:52
I've added information about credits - do you have any idea how I could add this to the query? – SB1987 Nov 24 '14 at 15:59
@SB1987 Not a problem just add `sum(Credits)` Check the answer updated – Pரதீப் Nov 24 '14 at 16:00
Added another query if you could give any advice on that would be most grateful! – SB1987 Nov 24 '14 at 16:19

score 0 · Answer 5 · answered Nov 24 '14 at 15:35

0

SELECT
   StudentId,Student,COUNT(*)
FROM TableName
WHERE EndDate BETWEEN '2014-01-01' AND '2014-11-24'
AND Status = 'Pass'
GROUP BY StudentId,Student

answered Nov 24 '14 at 15:35

hashbrown

3,438
1
19
37

1

Please add some explanation to your answer. Code-only answers are sometimes good enough, but code+explanation answers are always better – Barranka Nov 24 '14 at 15:58

score 0 · Answer 6 · answered Nov 24 '14 at 15:48

Not actually what you asked for, but potentially another option depending on what you want returned.

DECLARE @ClassEnrollments
TABLE   (
        StudentId   INT
        ,Student    VARCHAR(50)
        ,Module     VARCHAR(50)
        ,Status     VARCHAR(50)
        ,EndDate    DATE
        )

INSERT
INTO    @ClassEnrollments

VALUES   (12345678,'J Bloggs','Introduction','Pass','2014/09/01')
        ,(12345678,'J Bloggs','Advanced','Pass','2014/06/01')
        ,(23456789,'T Guy','Introduction','Pass','2013/05/25') -- Excluded
        ,(23456789,'T Guy','Advanced','Pass','2014/03/21')


SELECT  StudentId   
        ,Student    
        ,Module     
        ,Status     
        ,EndDate    
        ,COUNT(Module) OVER (PARTITION BY studentId) as #Modules
FROM    @ClassEnrollments
WHERE   EndDate 
        BETWEEN '2014-01-01' 
        AND     '2014-11-24'
ORDER BY
        StudentId
        ,Module

SQL to COUNT items associated with individuals

6 Answers6