CODEWITHSUNDEEP
sqlsql-serversql-server-2008aggregate-functions
SB1987
SB1987

Reputation: 23

SQL to COUNT items associated with individuals

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?

Upvotes: 2

Views: 180

Answers (6)

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 0

Pixelated
Pixelated

Reputation: 1651

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

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

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

Upvotes: 1

hashbrown
hashbrown

Reputation: 3516

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

Upvotes: 0

Matt
Matt

Reputation: 15061

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

Upvotes: 0

LittleSweetSeas
LittleSweetSeas

Reputation: 7074

Try this:

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

Upvotes: 0

Related Questions