Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Unanswered: How to get records in groups

    Hi,
    I have a Attendance table named say Attendance which has Class and Status along with some other fields.Class will have the name of the class the student is in,whereas the status will have the Absent/Present data.There is another table named say ClassMaster which is the master which will have all the classnames and there is a column of ClassTeacher which will have the EmployeeCode.This employeeCode comes from a table EmployeeMaster.My requirement is i want to get the count of Absent students in Attendance table depending on the Class.For Example if there are two class say ClassA and ClassB,then i should get the count of the Students from Attendance table that belong to Class A and Class B.How cn i do this in one Query.Please guide

  2. #2
    Join Date
    Oct 2009
    Posts
    27
    hi use this reply,

    use group by statement for getting multiple count of absent students.

    CREATE TABLE #Attendance (Class VARCHAR(100),[Status] VARCHAR(100),StudentName VARCHAR(100))
    INSERT INTO #Attendance (Class,[Status],StudentName) VALUES('Class A','Absent','XXX')
    INSERT INTO #Attendance (Class,[Status],StudentName) VALUES('Class A','Present','YYY')
    INSERT INTO #Attendance (Class,[Status],StudentName) VALUES('Class A','Present','AAA')
    INSERT INTO #Attendance (Class,[Status],StudentName) VALUES('Class A','Absent','ZZZ')

    INSERT INTO #Attendance (Class,[Status],StudentName) VALUES('Class B','Present','AAA')
    INSERT INTO #Attendance (Class,[Status],StudentName) VALUES('Class B','Absent','ZZZ')

    DECLARE @SQL AS VARCHAR(MAX)
    DECLARE @Class AS VARCHAR(400)
    SET @Class = '''Class A'',''Class B'''

    SET @SQL = 'SELECT Class,COUNT(StudentName) FROM #Attendance
    WHERE Class IN (' + @Class + ') and [Status]=''Absent''
    GROUP BY Class '

    EXECUTE(@SQL)

    DROP TABLE #Attendance

    If you have any doubts , let me know.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •