June 27, 2008 at 8:12 pm
When you run the first two sum statements w/o the division problem, the results are as follows:
Race Present Absent
W 3 4
B 3 4
H 3 4
I need to get a percentage of absences to total presence + absence. Therefore the results should look like this:
Race Present Absent Percent
W 3 4 57%
B 3 4 57%
H 3 4 57%
How can I do the calculations? And please give me the format, just don't say convert. Can someone actually type out the expression please. I'm totally new to sql server. I searched through the net, but can't find something so simple as this.
The original sql statement was:
SELECT a.ClassId, s.race,
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.sid=a.sid INNER JOIN Contact as s ON r.sid=s.sid WHERE a.ClassID=228626 GROUP BY a.ClassID, s.race
June 27, 2008 at 9:44 pm
I'm not sure what calculation you are looking for here. 3 and 4 give either 125% or 75%, how do you calculate 57%?
If you can give the algorithm, we can help.
The key is to get the data to sum as you need it, then you can easily apply division as needed to get a percentage.
select sum( case when fieldA = 'X' then 1 else o) 'first sum'
add in other fields, then do division with the entire expression "sum() / sum() * 100"
June 27, 2008 at 10:23 pm
SELECT a.ClassId, s.race,
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,
--new stuff here
Cast(SUM(case when a.Status='P' then 1 else 0 end) as float) /
SUM(case when a.Status in ('A','P') then 1 else null end) as percentage
FROM Attendance as a
INNER JOIN Roster as r ON r.sid=a.sid
INNER JOIN Contact as s ON r.sid=s.sid
WHERE a.ClassID=228626
GROUP BY a.ClassID, s.race
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply