June 27, 2008 at 8:24 am
I have the following sql statement:
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
What I need is a percentage of absences to total presences and absences.
Percent absent= absent / (present + absent)
How do I include this calculation into the statement above?
I did it this way:
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, SUM(case when a.Status='A' then 1 else 0 end) / (SUM(case when a.Status='A' then 1 else 0 end) + SUM(case when a.Status='P' then 1 else 0 end)) as PercentAbsent...
However, the PercentAbsent column returns 0.
Can someone help? I'm still learning how to do calculation in sql. Previously I did all my calculation in code, but now that I understand sql server can do all this, this will cut down on my coding big time.
Any help would be appreciated.
June 27, 2008 at 8:29 am
HI,
Please could you tell us what Present and Absent values are so that we have data to work with 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 27, 2008 at 8:33 am
Please also note that if no one is absent or present then you will get a division by zero error.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 27, 2008 at 12:20 pm
You need to cast/convert at least one of the numbers to either float or decimal (or money, I guess) before you do the division.
Count returns an integer value. Math with integers returns integers.
If, for example, someone was absent 1 time and present 9 times, you would end up calculating 1/10. In decimal/float, this is .1; In integers, it can't give you a fraction, so it returns 0.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 12:29 pm
I would try something like this
SELECT ClassId, race
, CASE WHEN SUM(Total) > 0 THEN (SUM(Absent) * 100.00)/SUM(Total) ELSE 0.00 END) as PercentAbsent
FROM (
SELECT a.ClassId, s.race,
(case when a.Status='A' then 1 else 0 end) AS Absent, 1 AS Total
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 ) AS t1
GROUP BY ClassID, race
June 27, 2008 at 5:24 pm
Hi all,
Kaushal I see what you trying to do but changing the zero base.
GSquare is prob right, but we still won't wont' know without a schema and some data 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 27, 2008 at 8:08 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
June 30, 2008 at 8:11 am
Try this, see if it does what you need:
;with Main as
(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)
select ClassID, Race, Present, [Absent],
cast([absent] as float)/cast(present+[absent] as float)
from Main
order by ClassID, Race
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2008 at 1:16 pm
How can I do the calculations? And please give me the format, just don't say convert.
FYI, google treats me well for this kind of stuff. eg. If I went to google and typed in "sql 2005 convert int to float" the first link is what you'd need, and you get the answer in less than 1 second. If someone throws out a new keyword at you like 'convert' and you don't know what it is or how to use it, give google a quick try, if you can't find the answer quickly, then it's probably worth further questions.
It's also typically good to try a quick search since it helps sharpen your ability to find/read articles in general.
Good luck with learning SQL 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply