June 24, 2008 at 4:39 pm
In a previous post, someone gave me a solution on how to sum the number of times a student has been absent or present. Here is the procedure:
COUNT(*) AS TotalRows, SUM(case when Status='P' then 1 else 0 end) AS Present, SUM(case when Status='A' then 1 else 0 end) AS Absent
FROM Attendance WHERE sid=studentnumber
Now, how can I expand this with a join. You see, I need to join the Attendance table with the Roster table in order to get the full name of the student that matches its student number in Attendance. For each student I would now need their name and the total presences and absences so that the return would have the following format:
Example:
studentid-student's name-total absences-total presences
00001 Joe Smith 0 2
I've thought about maybe doing two different processes. But I think that will slow the web app big time, particularly if I have a lot of students.
If it is possible to do it with just one sql statement it would be great! If anyone has any idea about how to do a join and use the count aggregate, let me know. I'd appreciate it.
June 24, 2008 at 9:00 pm
Hi Mark,
I think this could be what you're after:
IF OBJECT_ID('tempdb.dbo.#Attendance') IS NOT NULL
DROP TABLE #Attendance
GO
IF OBJECT_ID('tempdb.dbo.#Roster') IS NOT NULL
DROP TABLE #Roster
GO
CREATE TABLE #Attendance
(
at_studentId INTEGER,
at_status VARCHAR(40)
)
CREATE TABLE #Roster
(
st_studentId INTEGER IDENTITY,
st_name VARCHAR(40)
)
INSERT INTO #Attendance (at_studentId,at_status)
SELECT 1,'P' UNION ALL
SELECT 1,'A' UNION ALL
SELECT 2,'P' UNION ALL
SELECT 2,'P' UNION ALL
SELECT 3,'A' UNION ALL
SELECT 3,'P' UNION ALL
SELECT 4,'P' UNION ALL
SELECT 4,'A' UNION ALL
SELECT 4,'A' UNION ALL
SELECT 5,'P' UNION ALL
SELECT 5,'P' UNION ALL
SELECT 5,'A'
INSERT INTO #Roster (st_name)
SELECT 'Michael' UNION ALL
SELECT 'Alfred' UNION ALL
SELECT 'Stuart' UNION ALL
SELECT 'Rebecca' UNION ALL
SELECT 'Sam'
SELECT st_studentId AS [Student ID],st_name AS [Student Name],
SUM(CASE WHEN at_status='P' THEN
1
ELSE
0
END) AS [Total Present],
SUM(CASE WHEN at_status='A' THEN
1
ELSE
0
END) AS [Total Absent]
FROM #Attendance INNER JOIN #Roster ON st_studentId=at_studentId
GROUP BY st_studentId,st_name
ORDER BY st_studentId
Go and have a search for JOIN and you'll see all the different types, left, right inner outer..
Cheers,
Jim.
June 24, 2008 at 9:01 pm
COUNT(*) AS TotalRows, SUM(case when Status='P' then 1 else 0 end) AS Present, SUM(case when Status='A' then 1 else 0 end) AS Absent
FROM Attendance WHERE sid=studentnumber
Now, how can I expand this with a join. You see, I need to join the Attendance table with the Roster table in order to get the full name of the student that matches its student number in Attendance. For each student I would now need their name and the total presences and absences so that the return would have the following format:
Example:
studentid-student's name-total absences-total presences
00001 Joe Smith 0 2
I've thought about maybe doing two different processes. But I think that will slow the web app big time, particularly if I have a lot of students.
If it is possible to do it with just one sql statement it would be great! If anyone has any idea about how to do a join and use the count aggregate, let me know. I'd appreciate it.
How will you join from Attendance to Roster? Would that be on the student-id? Try this:
SELECT a.sid AS student-id
,r.student-name
,SUM(case when a.Status = 'P' then 1 else 0 end) AS Present
,SUM(case when a.Status = 'A' then 1 else 0 end) AS Absent
FROM Attendance AS a
INNER JOIN Roster AS r ON r.student-id = a.sid
WHERE a.sid = studentnumber
GROUP BY a.sid, r.student-name
BTW - where does studentnumber come from? This assumes that there is a column in Roster named student-id and the student-name[/] column also exists. Change these to the actual columns in the Roster table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply