Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-08-06 : 17:34:01
|
| I just tried to extend a SELECT to calculate percentages.Never realized it might be a problem..SELECT SUM(CASE Completed WHEN 1 then 1 else 0) as Total, Total*100.0/count(*) as Pct FROM ...Gives "Invalid Column Name "Total" in the reference Total*100.0/count(*)SamC |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-08-06 : 17:49:55
|
| Either you have to repeat the expressionSELECT SUM(CASE Completed WHEN 1 then 1 else 0 end) as Total, SUM(CASE Completed WHEN 1 then 1 else 0 end )*100.0/count(*) as Pct FROM ... or you could use a derived table(You should also add some check that the count is non-zero)select total,total*100.0/case when cnt = 0 then 1 else cnt end as pct from (select SUM(CASE Completed WHEN 1 then 1 else 0 end ) as total, count(*) as cnt from ... ) sed: missing the end for the case expressions.Edited by - LarsG on 08/06/2002 17:51:39 |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-08-06 : 18:25:52
|
| Thanks Lars -For the resolution of the SELECT and noticing the potential div/0 problem.I decided to code the preventative measure you suggested.SamC |
|
|
|
|
|
|
SELECT question