June 24, 2008 at 10:07 am
I have a conundrum. I have a table called "Attendance" to track student attendance. In the table there is a column called Status. Each day, each student is written into the database as either present or absent. Now I would like to have a stored procedure that counts the total number of presences and absences. How can I do this with the COUNT function. Is it possible to use multiple SELECT statements in a stored procedure to get two sets of results? I tried and it didn't work. I just got the results of the first SELECT statement. I wrote:
SELECT COUNT(*) AS TotalRows, COUNT(Status) AS NonNULLRows
FROM Attendance WHERE sid=studentnumber AND Status='P'
Does anyone have a technique to get the total number of presences or absences using the sql side. And if so, I would need to do the same for multiple students.
Any help, or better approach, would be appreciated
June 24, 2008 at 10:14 am
SELECT
COUNT(*) AS TotalRows
,COUNT(case when Status='P' then 1 else 0 end) AS Present
,COUNT(case when Status='A' then 1 else 0 end) AS Absent
FROM Attendance WHERE sid=studentnumber
---------------------------------------
elsasoft.org
June 24, 2008 at 11:27 am
jezemine (6/24/2008)
SELECTCOUNT(*) AS TotalRows
,COUNT(case when Status='P' then 1 else 0 end) AS Present
,COUNT(case when Status='A' then 1 else 0 end) AS Absent
FROM Attendance WHERE sid=studentnumber
Jezemine: SUM for numbers, COUNT for nulls/non-nulls:
(I'm sure it's just a slip!)
Modified:
SELECT
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
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2008 at 11:32 am
of course. oops!
---------------------------------------
elsasoft.org
June 24, 2008 at 11:34 am
Thanks guys! This helps alot!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply